四、排序和分组
1.每个教师所教授的科目种类的数量
题目描述
代码编写
select teacher_id,count(distinct subject_id) as cnt from teacher
group by teacher_id
2.查询近30天活跃用户数
题目描述
代码编写
select activity_date as day,count(distinct user_id) as active_users from Activity
where datediff('2019-07-27',activity_date)<30 and activity_date<='2019-07-27'
group by activity_date
注意:题目要限制 activity_date<='2019-07-27'或者是datediff>=0,不然返回的结果会查询到07-27之后的数据。
3.销售分析
题目描述
代码编写
select product_id,product_name from Product
where product_id in (
select product_id from Sales
group by product_id
having min(sale_date)>='2019-01-01' and max(sale_date)<='2019-03-31'
)
题目要求“仅在2019-01-01至2019-03-31之间出售的商品”,请注意是“仅”,说明商品只能在该段时间出售,其余时间下架,用BTWEEN...AND...并不能突出“仅”,转而采用MAX(),MIN()函数。
4.超过5名学生的课
题目描述
代码编写
select class from Courses
group by class
having count(student)>=5
5.求关注者的数量
题目描述
代码编写
select user_id,count(follower_id) as followers_count from Followers
group by user_id
order by user_id
6.只出现一次的最大数字
题目描述
代码编写
做法一:
select max(num) as num from (
select num from MyNumbers
group by num
having count(num)=1) as my
易错思路:
select max(num)
from my_numbers
group by num
having count(*)=1
select里的聚合函数是针对每一分组的,不能直接作用于聚合键,执行后返回的结果是每个分组的最大值。
做法二(参考网友做法) :
select
case when count(*)=1 then num else null end as num
from MyNumbers
group by num
order by num desc
limit 1
做法二解决了上述思路的错误,通过对筛选结果进行降序排序,并返回1个结果,就可以得到正确的返回结果。
7.买下所有产品的客户
题目描述
题目思路:
1.按照顾客id进行分组,并统计他们购买的不重复的商品的数量
2.统计店里商品的总数量
3.筛选出购买的不重复的商品数量和商品总数量相等的顾客id
代码编写
select customer_id from Customer
group by customer_id
having count(distinct product_key)=(select count(product_key) from Product)