【SQL高频练习带刷】day5：排序和分组

题目一：每位教师所教授的科目种类的数量

题目要求：

查询每位老师在大学里教授的科目种类的数量。

以 任意顺序 返回结果表。

表结构：

 运行结果示例：

思路：

        这道题目非常简单，使用count（）函数结合group By 教师id分组即可，注意课程要去重。

运行代码示例：

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

题目二：查询近30天活跃用户数

题目要求：

编写解决方案，统计截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。

以 任意顺序 返回结果表。

表结构：

 运行结果示例：

思路：

        这道题的难点在于要如何实现对“近30天”的条件进行筛选。我们可以使用时间函数datediff（）来计算时间直接的差值，限定差值介于0-30.

运行代码示例：

select activity_date as day,count(distinct user_id) as active_users
from Activity
where DATEDIFF("2019-07-27",activity_date) BETWEEN 0 AND 29
group by activity_date

题目三：销售分析

题目要求：

编写解决方案，报告 2019年春季 才售出的产品。即 仅 在 2019-01-01 （含）至 2019-03-31 （含）之间出售的商品。

以 任意顺序 返回结果表。

表结构：

 运行结果示例：

思路：

        这道题难度不大，但胜在很巧妙。因为题目要求查找的数据是“仅”在 2019-01-01 （含）至 2019-03-31 （含）之间出售的商品。如何筛选出满足“仅”这个条件的数据呢？比较常规的解法是使用子查询，但是我在题解里看到一个很巧的解法，使用SUM(s.sale_date BETWEEN '2019-01-01' AND '2019-03-31') = COUNT(*)条件进行筛选，如果该组数据中的count总和等于满足条件的数据数，说明该商品只在春季售卖。

运行代码示例：

select p.product_id,product_name
from Product p
left join Sales s using(product_id)
GROUP BY s.product_id
HAVING SUM(s.sale_date BETWEEN '2019-01-01' AND '2019-03-31') = COUNT(*)

题目四：超过5名学生的课

题目要求：

查询 至少有5个学生 的所有班级。

以 任意顺序 返回结果表

表结构：

 运行结果示例：

思路：

        这道题难度不大，考察了一下having子句。

运行代码示例：

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

题目五：求关注者的数量

题目要求：

编写解决方案，对于每一个用户，返回该用户的关注者数量。

按 user_id 的顺序返回结果表。

表结构：

 运行结果示例：

思路：

       这道题也很简单（这组题目简单到不像是力扣的....），考察了group by和order by子句的联合使用。

运行代码示例：

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

 题目六：只出现一次的最大数字

题目要求：

单一数字 是在 MyNumbers 表中只出现一次的数字。

找出最大的 单一数字 。如果不存在 单一数字 ，则返回 null 。

表结构：

 运行结果示例：

思路：

        这道题刚拿到手会有点懵，一说最大数字第一反应是max（）或者rank（），但是又要找到只出现一次的，又要返回null。然后就突然不知道怎么做了。但是突然想到或许可以根据num分组，计算每组数据量，数据量为1的肯定就是没有出现过的了，再在没有这些数据中找到最大值就OK。其实就是一个子查询。至于找不到返回null这一点也就很容易就达成了。

运行代码示例：

select max(num) as num
from(select num from MyNumbers group by num having count(num) = 1) t

 题目七：买下所有产品的客户

题目要求：

编写解决方案，报告 Customer 表中购买了 Product 表中所有产品的客户的 id。

返回结果表 无顺序要求 。

表结构：

 运行结果示例：

思路：

      虽然这道题难度是中等，但是有了前面几题的铺垫，很容易想到使用having子句限定条件，让客户表中的去重后的产品数与产品表中的数据量相等即可。需要注意的是这道题条件注明了product_key 是 Product 表的外键，有了外键约束就可以保证 使用数量对比不会出错，如果没有这个前置条件，我们也可以先用where子句判断一下，筛掉product_key不在产品表中的数据。

（having是在分组后对数据进行过滤；where是在分组前对数据进行过滤
   having后面可以使用聚合函数；where后面不可以使用聚合）

运行代码示例：

select customer_id
from Customer
group by customer_id
having count(distinct product_key) = (select count(*) from Product)