sql-排列分组

2356. 每位教师所教授的科目种类的数量 - 力扣（LeetCode）

select teacher_id,
    count(distinct subject_id) as cnt
from Teacher 
GROUP BY teacher_id 

 

　　按照老师id分类

 

1141. 查询近30天活跃用户数 - 力扣（LeetCode）

# Write your MySQL query statement below
select activity_date as day,
    count(distinct user_id) as active_users 
from Activity 
WHERE activity_date BETWEEN '2019-06-28' AND '2019-07-27'
GROUP BY activity_date 

　　注意：关于时间这块有好几种写法

WHERE DATEDIFF("2019-07-27",activity_date) BETWEEN 0 AND 29

WHERE DATEDIFF(activity_date，"2019-06-28") BETWEEN 0 AND 29

WHERE activity_date BETWEEN DATE_SUB('2019-07-27',INTERVAL 29 day) and '2019-07-27'

还要date_add

1084. 销售分析 III - 力扣（LeetCode）

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

如果要使用group by ，select中的非聚合列（avg，sum...）都包括进去

having：可以用于限定聚合列 ，常跟着group by连用

where ：不能限定聚合列

596. 超过 5 名学生的课 - 力扣（LeetCode）

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

　　注意脑子不能像博主糊涂，写成count(class)

count(calss)，统计class非null的行数与student无关，应该是统计每个班上有多少人数

count应该是学生，非null的行数

1729. 求关注者的数量 - 力扣（LeetCode）

select user_id,
    count(distinct follower_id) as followers_count 
from Followers 
group by user_id 

　　注意 distinct 去重

619. 只出现一次的最大数字 - 力扣（LeetCode）

SELECT 
(
    SELECT num
    from MyNumbers 
    group by num
    HAVING count(num) = 1
    order by num desc
    limit 1
) as num

　　

1045. 买下所有产品的客户 - 力扣（LeetCode）

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

　　注意 ：编写解决方案，报告 Customer 表中购买了 Product 表中所有产品的客户的 id。所以having要进行筛选