596. 超过5名学生的课
表: Courses
+-------------+---------+ | Column Name | Type | +-------------+---------+ | student | varchar | | class | varchar | +-------------+---------+ 在 SQL 中，(student, class)是该表的主键列。 该表的每一行表示学生的名字和他们注册的班级。
查询 至少有5个学生 的所有班级。
以 任意顺序 返回结果表。
示例 1:
输入: Courses table: +---------+----------+ | student | class | +---------+----------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | +---------+----------+ 输出: +---------+ | class | +---------+ | Math | +---------+ 解释: -数学课有6个学生，所以我们包括它。 -英语课有1名学生，所以我们不包括它。 -生物课有1名学生，所以我们不包括它。 -计算机课有1个学生，所以我们不包括它。
select class
from courses
group by class
having count(distinct student)>=5;
- 先分组然后算出每门课有的学生用having过滤
- 测试用例里可能有同一个学生选两次同一门课的记录所以要用distinct
1729. 求关注者的数量
表： Followers
+-------------+------+ | Column Name | Type | +-------------+------+ | user_id | int | | follower_id | int | +-------------+------+ (user_id, follower_id) 是这个表的主键（具有唯一值的列的组合）。 该表包含一个关注关系中关注者和用户的编号，其中关注者关注用户。
编写解决方案，对于每一个用户，返回该用户的关注者数量。
按 user_id 的顺序返回结果表。
示例 1：
输入： Followers 表： +---------+-------------+ | user_id | follower_id | +---------+-------------+ | 0 | 1 | | 1 | 0 | | 2 | 0 | | 2 | 1 | +---------+-------------+ 输出： +---------+----------------+ | user_id | followers_count| +---------+----------------+ | 0 | 1 | | 1 | 1 | | 2 | 2 | +---------+----------------+ 解释： 0 的关注者有 {1} 1 的关注者有 {0} 2 的关注者有 {0,1}
select user_id,count(follower_id) as followers_count
from followers
group by user_id
order by user_id;
- 分组后计算
619. 只出现一次的最大数字
MyNumbers 表：
+-------------+------+ | Column Name | Type | +-------------+------+ | num | int | +-------------+------+ 该表可能包含重复项（换句话说，在SQL中，该表没有主键）。 这张表的每一行都含有一个整数。
单一数字 是在 MyNumbers 表中只出现一次的数字。
找出最大的 单一数字 。如果不存在 单一数字 ，则返回 null 。
示例 1：
输入： MyNumbers 表： +-----+ | num | +-----+ | 8 | | 8 | | 3 | | 3 | | 1 | | 4 | | 5 | | 6 | +-----+ 输出： +-----+ | num | +-----+ | 6 | +-----+ 解释：单一数字有 1、4、5 和 6 。 6 是最大的单一数字，返回 6 。
select max(num) as num from
(select num from mynumbers group by num having count(num)=1) as t
- 先分组搜索出只出现过一次的数字。
- 再通过max函数搜索出最大的数字。
1045. 买下所有产品的客户
Customer 表：
+-------------+---------+ | Column Name | Type | +-------------+---------+ | customer_id | int | | product_key | int | +-------------+---------+ 该表可能包含重复的行。 customer_id 不为 NULL。 product_key 是 Product 表的外键(reference 列)。
Product 表：
+-------------+---------+ | Column Name | Type | +-------------+---------+ | product_key | int | +-------------+---------+ product_key 是这张表的主键（具有唯一值的列）。
编写解决方案，报告 Customer 表中购买了 Product 表中所有产品的客户的 id。
返回结果表 无顺序要求 。
示例 1：
输入： Customer 表： +-------------+-------------+ | customer_id | product_key | +-------------+-------------+ | 1 | 5 | | 2 | 6 | | 3 | 5 | | 3 | 6 | | 1 | 6 | +-------------+-------------+ Product 表： +-------------+ | product_key | +-------------+ | 5 | | 6 | +-------------+ 输出： +-------------+ | customer_id | +-------------+ | 1 | | 3 | +-------------+ 解释： 购买了所有产品（5 和 6）的客户的 id 是 1 和 3 。
select customer_id
from customer
group by customer_id
having count(distinct product_key)=(select count(product_key) from product);
- 先分组查询然后统计每个id购买过的产品id数量以及产品表所有产品的数量。
- 第一个count中必须加distinct，因为一个用户可能会买同一个产品两次。
1731. 每位经理的下属员工数量
表：Employees
+-------------+----------+ | Column Name | Type | +-------------+----------+ | employee_id | int | | name | varchar | | reports_to | int | | age | int | +-------------+----------+ employee_id 是这个表中具有不同值的列。 该表包含员工以及需要听取他们汇报的上级经理的 ID 的信息。 有些员工不需要向任何人汇报（reports_to 为空）。
对于此问题，我们将至少有一个其他员工需要向他汇报的员工，视为一个经理。
编写一个解决方案来返回需要听取汇报的所有经理的 ID、名称、直接向该经理汇报的员工人数，以及这些员工的平均年龄，其中该平均年龄需要四舍五入到最接近的整数。
返回的结果集需要按照 employee_id 进行排序。
示例 1:
输入： Employees 表： +-------------+---------+------------+-----+ | employee_id | name | reports_to | age | +-------------+---------+------------+-----+ | 9 | Hercy | null | 43 | | 6 | Alice | 9 | 41 | | 4 | Bob | 9 | 36 | | 2 | Winston | null | 37 | +-------------+---------+------------+-----+ 输出： +-------------+-------+---------------+-------------+ | employee_id | name | reports_count | average_age | +-------------+-------+---------------+-------------+ | 9 | Hercy | 2 | 39 | +-------------+-------+---------------+-------------+ 解释： Hercy 有两个需要向他汇报的员工, 他们是 Alice and Bob. 他们的平均年龄是 (41+36)/2 = 38.5, 四舍五入的结果是 39.示例 2:
输入： Employees 表： +-------------+---------+------------+-----+ | employee_id | name | reports_to | age | |-------------|---------|------------|-----| | 1 | Michael | null | 45 | | 2 | Alice | 1 | 38 | | 3 | Bob | 1 | 42 | | 4 | Charlie | 2 | 34 | | 5 | David | 2 | 40 | | 6 | Eve | 3 | 37 | | 7 | Frank | null | 50 | | 8 | Grace | null | 48 | +-------------+---------+------------+-----+ 输出： +-------------+---------+---------------+-------------+ | employee_id | name | reports_count | average_age | | ----------- | ------- | ------------- | ----------- | | 1 | Michael | 2 | 40 | | 2 | Alice | 2 | 37 | | 3 | Bob | 1 | 37 | +-------------+---------+---------------+-------------+
1.第一种外连接
select a.employee_id, a.name, count(distinct b.employee_id) as reports_count,round(avg(b.age),0) as average_age
from employees a
left join employees b on a.employee_id = b.reports_to
group by a.employee_id,a.name
having count(b.employee_id)>0
order by a.employee_id;
- 左连接后会保留没有汇报员工的经理的列，需要把没有汇报员工的列筛选掉。
2.自连接
select a.employee_id as 'employee_id', a.name as 'name', count(b.employee_id) as 'reports_count', round(avg(b.age), 0) as 'average_age'
from Employees a inner join Employees b
on a.employee_id = b.reports_to
group by a.employee_id
order by a.employee_id
- 自连接可以自动筛选掉没有下属员工的列，就不需要having或者where进行筛选了。
扫一扫
2484
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
