23-29题排序和分组- 高频 SQL 50 题基础版

1. 相关知识点

• where 和having的区别
  • where 是分组之前筛选数据
  • having 是分组之后筛选数据
• 相关函数

函数	含义
group by	分组
distinct	去重
date_sub(‘2019-07-27’, interval 30 day)	日期相减
date_add(‘2019-07-27’, interval 30 day)	日期相加
datediff(‘2019-07-27’,activity_date)<30	日期相差范围

2. 例子

2.23 - 每位教师所教授的科目种类的数量

• 考点： 排序和分组
  
  
  

select
    teacher_id,count(distinct subject_id) cnt 
from
    Teacher 
group by
    teacher_id;

2.24 - 查询近30天活跃用户数

• 排序和分组
  
  
  
  

-- 日期相减 date_sub('2019-07-27', interval 30 day)获取新的日期
-- 日期范围 datediff('2019-07-27',activity_date)<30

-- where activity_date > date_sub('2019-07-27', interval 30 day)
-- and activity_date <= '2019-07-27'=where datediff('2019-07-27',activity_date)<30

select
    activity_date day,count(distinct user_id) active_users 
from
    Activity 
where 
    datediff('2019-07-27',activity_date)<30

GROUP BY 
    activity_date;

2.25 - 销售分析III

select
    p.product_id,p.product_name 
from
    Sales s left join Product  p on s.product_id=p.product_id 
group by
    product_id
having
    min(sale_date) >="2019-01-01" and max(sale_date)<="2019-03-31";

2.26 - 超过5名学生的课

select 
    class
from
    Courses 
group by
    class    
having
    count(*)>=5;

2.27 - 求关注者的数量

select
    user_id,count(*) followers_count
from
    Followers 
group by
    user_id;

2.28 - 只出现一次的最大数字

select 
    (select
        num
    from
        MyNumbers 
    group by
        num 
    having
        count(num)=1
    order by 
        num 
    desc limit 
        1) 
as 
    num;

2.29 - 买下所有产品的客户

select
    c.customer_id 
from
    Customer c 
group by
    c.customer_id 
having
    count(c.product_key)=(select count(distinct product_key) from Product);