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