每日刷力扣SQL题(三)

2356.每位教师所教授的科目种类的数量

考察点：聚合函数 、不同系所教课程相同 所以可以用关键字distinct来区分

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

1141.查询近三十天活跃用户数

考察点：近三十天 包括当天 日期时间段应减去29天 

Select activity_date as day,count(distinct(user_id)) as active_users
from Activity
where activity_date >= '2019-07-27' - INTERVAl 29 DAY and activity_date <= '2019-07-27'
group by activity_date

更准确的解法：

1、使用Between and 关键字 和 判断activity_type是否为NULL

SELECT activity_date as day, count(distinct user_id) as active_users
FROM Activity
WHERE activity_date between date_sub("2019-07-27", interval 29 day) and "2019-07-27"  AND (activity_type is not null)
GROUP BY 1;

1084.销售分析|||

两种写法

其实题目要求“仅在2019-01-01至2019-03-31之间出售的商品”翻译过来就是“所有售出日期都在这个时间内”，也就是“在这个时间内售出的商品数量等于总商品数量”，这样就不难写出这个语句

select sales.product_id as product_id, product.product_name as product_name
from sales left join product on sales.product_id = product.product_id
group by product_id
having count(sale_date between '2019-01-01' and '2019-03-31' or null) = count(*)
 

Select s.product_id as product_id , product_name 
from Sales s left join Product p on s.product_id = p.product_id
group by s.product_id 
having count(IF(sale_date between '2019-01-01' and '2019-03-31',1,null)) = count(*)

其他解法：

SELECT DISTINCT
    p.product_id,
    p.product_name
FROM
    Product p
JOIN
    Sales s ON p.product_id = s.product_id
WHERE
    s.sale_date BETWEEN '2019-01-01' AND '2019-03-31'
    AND s.product_id NOT IN (
        SELECT
            s2.product_id
        FROM
            Sales s2
        WHERE
            s2.sale_date < '2019-01-01'
            OR s2.sale_date > '2019-03-31'
    );

596.超过5名学生的课

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

1729.求关注者的数量

select user_id , count(follower_id) as followers_count
from Followers
group by user_id
Order by user_id ASC;

619.只出现一次的最大数字

select Max(num) as num
from 
(select  num 
from MyNumbers
group by num 
having count(*) = 1 ) as Number