景观设计师如何做网站,珠海企业网站建设公,南京营销型网站建设公司,潍坊专业网站建设哪家好文章目录 主要内容一.SQL练习题1.1174-即时食物配送代码如下#xff08;示例#xff09;: 2.550-游戏玩法分析代码如下#xff08;示例#xff09;: 3.2356-每位教师所教授的科目种类的数量代码如下#xff08;示例#xff09;: 4.1141-查询近30天活跃用户数代码如下示例: 2.550-游戏玩法分析代码如下示例: 3.2356-每位教师所教授的科目种类的数量代码如下示例: 4.1141-查询近30天活跃用户数代码如下示例: 5.1084-销售分析代码如下示例: 6.596-超过5名学生的课代码如下示例: 7.1729-求关注者的数量代码如下示例: 8.619-只出现一次的最大数字代码如下示例: 9.1045-买下所有产品的客户代码如下示例: 10.1731-每位经理的下属员工数量代码如下示例: 总结 主要内容
LeetCode-高频SQL50题基础版21-30
一.SQL练习题 1.1174-即时食物配送 代码如下示例:
# Write your MySQL query statement below
Select round(avg(order_date customer_pref_delivery_date)*100,2) immediate_percentage
from (select *,row_number() over(partition by customer_id order by order_date) as rn from delivery
) a
where rn 12.550-游戏玩法分析 代码如下示例:
# Write your MySQL query statement belowselect round(avg(a.event_date is not null), 2) fraction
from (select player_id, min(event_date) as loginfrom activitygroup by player_id) p
left join activity a
on p.player_ida.player_id and datediff(a.event_date, p.login)13.2356-每位教师所教授的科目种类的数量 代码如下示例:
# Write your MySQL query statement below
select teacher_id,count(distinct subject_id) as cnt
from teacher
group by teacher_id;4.1141-查询近30天活跃用户数 代码如下示例:
# Write your MySQL query statement below
SELECT activity_date day, COUNT(DISTINCT user_id) active_users
FROM Activity
WHERE DATEDIFF(2019-07-27, activity_date) 30 AND DATEDIFF(2019-07-27, activity_date) 0
GROUP BY activity_date;5.1084-销售分析 代码如下示例:
# Write your MySQL query statement below
select p.product_id,product_name
from sales s,product p
where s.product_id p.product_id
group by p.product_id
having sum(sale_date 2019-01-01) 0
and sum(sale_date 2019-03-31) 0;6.596-超过5名学生的课 代码如下示例:
# Write your MySQL query statement below
select class
from courses
group by class
having count(distinct(student))5;
7.1729-求关注者的数量 代码如下示例:
# Write your MySQL query statement below
select user_id, count(follower_id) as followers_count
from followers
group by user_id
order by user_id;8.619-只出现一次的最大数字 代码如下示例:
# Write your MySQL query statement below
select max(num) as num
from (select num from MyNumbersgroup by num having count(num)1) as t ;9.1045-买下所有产品的客户 代码如下示例:
# Write your MySQL query statement below
select distinct c.customer_id
from customer c
left join product p
on c.product_key p.product_key
group by c.customer_id
having count(distinct p.product_key) (select count(distinct product_key)from product)10.1731-每位经理的下属员工数量 代码如下示例:
# Write your MySQL query statement below
select b.employee_id,b.name,count(a.reports_to) reports_count,round(avg(a.age),0) average_age
from employees a,employees b
where a.reports_to b.employee_id
group by a.reports_to
having reports_count ! 0
order by employee_id;总结
以上是今天要讲的内容练习了一些高频SQL题。
