来源：高频 SQL 50 题（基础版）leetcode.cn/studyplan/s…
1978. 上级经理已离职的公司员工
查找这些员工的 id，他们的薪水严格少于 $30000 并且他们的上级经理已离职。当一个经理离开公司时，他们的信息需要从员工表中删除掉，但是表中的员工的 manager_id 这一列还是设置的离职经理的 id 。
返回的结果按照 employee_id 从小到大排序。
查询结果如下所示：
思路 + SQL
- 先搜索出上级经理已经离职的员工，即 manager_id 在员工表里找不到对应数据的员工：
select
employee_id
from
Employees
where
manager_id not in (
select
employee_id
from
Employees
);
- 组合薪水少于 30000，加上 employee_id 排序，即可得到结果：
select
employee_id
from
Employees
where
salary < 30000
-- 上司已离职的条件
and manager_id not in (
select
employee_id
from
Employees
)
order by
employee_id;
626. 换座位
编写解决方案来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。
按 id 升序 返回结果表。
查询结果格式如下所示。
思路 + SQL
这里是相邻两个人，两两相互换座位，也就是 id 交换一下。
以 1 号和 2 号为例，1 号 + 1 变为 2 号，2 号 - 1 变为 1 号，就做好交换了。
但是在奇数数量的时候，需要判断多出来的那单独的最后一个人，那个人不需要换座位。
select
if(
-- 如果是偶数，直接 -1，奇数得做后续操作
id % 2 = 0,
id - 1,
if(
-- 如果该奇数是最后一位，不需要换座位，其他情况的奇数 +1
id = (
select
count(id)
from
Seat
),
id,
id + 1
)
) as id,
student
from
Seat
order by
id;
1341. 电影评分
请你编写一个解决方案：
- 查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
- 查找在
February 2020平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。
字典序 ，即按字母在字典中出现顺序对字符串排序，字典序较小则意味着排序靠前。
返回结果格式如下例所示。
示例 1：
思路 + SQL
- 首先，先做第一步，关联用户表和评分表：
select
*
from
Users u
inner join MovieRating mr on u.user_id = mr.user_id;
- 对上面的结果稍作修改，并做为表，通过此表搜索出评论电影数量最多的用户名。
select
name,
user_id,
count(*) as rating_count
from
(
select
u.name,
u.user_id
from
Users u
inner join MovieRating mr on u.user_id = mr.user_id
) t
group by
user_id
order by
rating_count desc,
name asc
limit
1;
- 解决第二个问题，需要关联电影列表以及评分表：
select
*
from
Movies m
inner join MovieRating mr on m.movie_id = mr.movie_id;
- 同样的，对上面的结果稍作修改，用来查询 2020 年 2 月的电影平均分中+字典序第一位的电影
select
movie_id,
title,
avg(rating) as avg_rating
from
(
select
m.movie_id,
m.title,
mr.rating
from
Movies m
inner join MovieRating mr on m.movie_id = mr.movie_id
where
created_at like '2020-02-%'
) t
group by
movie_id
order by
avg_rating desc,
title asc
limit
1;
- 将上面的两个查询结果声明为临时表，然后用
union all将两个查询连接起来：
-- 【with xxx as 查询】起手，说明要声明临时表
with most_rate_user as (
-- 评论最多 + 字典序最靠前用户的查询结果
select
name,
user_id,
count(*) as rating_count
from
(
select
u.name,
u.user_id
from
Users u
inner join MovieRating mr on u.user_id = mr.user_id
) t
group by
user_id
order by
rating_count desc,
name asc
limit
1
), highest_rate_movie as (
-- 2022 年 2 月评分最高 + 字典序最靠前的电影查询结果
select
movie_id,
title,
avg(rating) as avg_rating
from
(
select
m.movie_id,
m.title,
mr.rating
from
Movies m
inner join MovieRating mr on m.movie_id = mr.movie_id
where
created_at like '2020-02-%'
) t
group by
movie_id
order by
avg_rating desc,
title asc
limit
1
)
-- 以上面的两个临时表为基础进行简单的查询
select
name as results
from
most_rate_user
-- 使用 union all，避免用户名和电影名一样导致结果被合并
union all
select
title as results
from
highest_rate_movie;
1321. 餐馆营业额变化增长
你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）。
计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。average_amount 要 保留两位小数。
结果按 visited_on 升序排序。
返回结果格式的例子如下。
思路 + SQL 1 （笛卡尔积 + 分组计算）
因为要计算 7 天销售额，所以需要从第 7 天开始才开始计算。
所以可以先让获取有消费日期对应的日期表（distinct Customer 表中的 visited_on 就可以轻松获得），然后和 Customer 表做笛卡尔积，并做相关的数据过滤（顾客的消费日期范围在日期表指定日期的当天 + 前六天）。
同时关联用户最早消费的日期，用于判断最早日期后的第 7 天是哪一天（最早消费日期起的 7 天为第一个时间段）。
筛选后，得到左侧日期为统计日期下，对应的 7 天内销售数据。
select
*
from
-- 开店的所有日期表
(
select
distinct visited_on
from
Customer
) t1,
Customer t2,
-- 第一天日期（用于计算是否是第七天开始）
(
select
min(visited_on) as min_date
from
Customer
) t3
where
-- 过滤条件 1：统计的日期 >= 消费者访问日期，但不大于 7 天
dateDiff(t1.visited_on, t2.visited_on) <= 6
and dateDiff(t1.visited_on, t2.visited_on) >= 0
-- 过滤条件 2：统计日期在最早记录日期算起的第 7 天及往后
and dateDiff(t1.visited_on, min_date) >= 6
order by
t1.visited_on;
最后 group by 一下日期，用聚合函数计算总数和平均值即可得到答案：
select
t1.visited_on,
sum(t2.amount) as amount,
round(sum(t2.amount) / 7, 2) as average_amount
from
-- 开店的所有日期表
(
select
distinct visited_on
from
Customer
) t1,
Customer t2,
-- 第一天日期（用于计算是否是第七天开始）
(
select
min(visited_on) as min_date
from
Customer
) t3
where
-- 过滤条件 1：当天的日期 >= 消费者访问日期，但不大于 7 天
dateDiff(t1.visited_on, t2.visited_on) <= 6
and dateDiff(t1.visited_on, t2.visited_on) >= 0
-- 过滤条件 2：当天日期在最早记录日期算起的第 7 天及往后
and dateDiff(t1.visited_on, min_date) >= 6
group by
t1.visited_on
order by
t1.visited_on;
思路 + SQL 2 （思路 1 的 SQL 优化）
下面写法的优化，更符合常规写法。
select
t1.visited_on,
sum(t2.amount) as amount,
round(sum(t2.amount) / 7, 2) as average_amount
from
-- 开店的所有日期表
(
select
distinct visited_on
from
Customer
) t1
-- 连接条件：当天的日期 >= 消费者访问日期，但不大于 7 天
cross join Customer t2
on dateDiff(t1.visited_on, t2.visited_on) between 0 and 6
group by
t1.visited_on
having
-- 分组后条件：此条数据的日期在最早记录日期算起的第 7 天及往后
dateDiff(
t1.visited_on,
(
select
min(visited_on)
from
Customer
)
) >= 6
order by
t1.visited_on;
602. 好友申请 II ：谁有最多的好友
编写解决方案，找出拥有最多的好友的人和他拥有的好友数目。
生成的测试用例保证拥有最多好友数目的只有 1 个人。
查询结果格式如下例所示。
思路 + SQL
其实就是数 requester_id 与 accepter_id 里所有数字的重复数量，哪个重复数量多就返回哪个数字，就可以得到。
- 先使用
UNION ALL将 requester_id 和 accepter_id 拼接起来：
select
id,
count(*) as num
from
(
select
requester_id as id
from
RequestAccepted
union all
select
accepter_id as id
from
RequestAccepted
) as t
group by
id
order by
num desc
limit
1;
- 然后对这个查询结果作为表去分组排序，取计数次数的倒序第一位，就是最多的好友 id 对应的数量。
select
id,
count(*) as num
from
(
select
requester_id as id
from
RequestAccepted
union all
select
accepter_id as id
from
RequestAccepted
) as t
group by
id
order by
num desc
limit
1;
585. 2016年的投资
编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：
- 他在 2015 年的投保额 (
tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。 - 他所在的城市必须与其他投保人都不同（也就是说 (
lat, lon) 不能跟其他任何一个投保人完全相同）。
tiv_2016 四舍五入的 两位小数 。
查询结果格式如下例所示。
思路 + SQL 1（常规思路）
- 先内连接查询出所有存在相同的 2015 年投保额情况（需要排除自身）
select
*
from
Insurance i1
inner join Insurance i2 on i1.tiv_2015 = i2.tiv_2015
and i1.pid != i2.pid;
- 排除城市相同的情况，即在 lat 和 lon 分组聚合下，计数大于 1 的情况：
select
*
from
Insurance i1
inner join Insurance i2 on i1.tiv_2015 = i2.tiv_2015
and i1.pid != i2.pid
where
(i1.lat, i1.lon) in (
select
lat,
lon
from
Insurance
group by
lat,
lon
having
count(*) = 1
);
- 将上面的查询结果去重后进行
SUM聚合计算，即可得到结果：
select
round(sum(tiv_2016), 2) as tiv_2016
from
(
select
-- 对符合条件的查询结果去重
distinct i1.pid,
i1.tiv_2016
from
Insurance i1
-- 交集得到存在相同的 2015 年投保额情况（需要排除自身）
inner join Insurance i2 on i1.tiv_2015 = i2.tiv_2015
and i1.pid != i2.pid
where
-- 排除城市有重复的条件查询
(i1.lat, i1.lon) in (
select
lat,
lon
from
Insurance
group by
lat,
lon
having
count(*) = 1
)
) as t;
思路 + SQL 2（优化简约版）
上面的 SQL 看起来可读性还是比较差的，其实可以两个条件都放进 where 里，可读性就会好很多。
- 先查询出 2015 年的投保额存在相同的相关额度：
select
tiv_2015
from
Insurance
group by
tiv_2015
having
count(*) > 1;
- 再查询出所在的城市必须与其他投保人都不同的情况，也就是 lat 和 lon 分组后，计数只有 1 的查询结果：
select
lat,
lon
from
Insurance
group by
lat,
lon
having
count(*) = 1;
- 最后将这两个查询合并应用，即可获得答案：
select
-- 四舍五入两位小数的总和
round(sum(tiv_2016), 2) as tiv_2016
from
Insurance
where
-- 相同额度表
tiv_2015 in (
select
tiv_2015
from
Insurance
group by
tiv_2015
having
count(*) > 1
)
-- 唯一城市表
and (lat, lon) in (
select
lat,
lon
from
Insurance
group by
lat,
lon
having
count(*) = 1
);
185. 部门工资前三高的所有员工
表: Employee
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id 是该表的主键列(具有唯一值的列)。
departmentId 是 Department 表中 ID 的外键（reference 列）。
该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。
表: Department
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id 是该表的主键列(具有唯一值的列)。
该表的每一行表示部门ID和部门名。
公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
编写解决方案，找出每个部门中 收入高的员工 。
以 任意顺序 返回结果表。
返回结果格式如下所示。
示例 1:
输入:
Employee 表:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
输出:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Joe | 85000 |
| IT | Randy | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释:
在IT部门:
- Max的工资最高
- 兰迪和乔都赚取第二高的独特的薪水
- 威尔的薪水是第三高的
在销售部:
- 亨利的工资最高
- 山姆的薪水第二高
- 没有第三高的工资，因为只有两名员工
思路 + SQL 1（窗口函数）
看到了排序规则，就很容易想到窗口函数的 dense_rank，用 dense_rank得到薪资排名后，用 where 获取即可。
- 关联 Employee和 Department表：
select
*
from
Employee as e
inner join Department as d on e.departmentId = d.id;
- 用关联表做窗口函数查询，拓展得到薪水排名：
select
*,
dense_rank() over (
partition by d.id
order by
e.salary desc
) as salaryRank
from
(
Employee as e
inner join Department as d on e.departmentId = d.id
);
- 最后做 where 筛选出每个部门的前三名即可。
select
Department,
Employee,
Salary
from
(
select
d.name as department,
e.name as employee,
e.salary,
-- 部门分组，并用 dense_rank 进行薪资排名
dense_rank() over (
partition by d.id
order by
e.salary desc
) as salaryRank
from
(
Employee as e
inner join Department as d on e.departmentId = d.id
)
) as t
where
salaryRank <= 3;
思路 + SQL 2 （子查询土办法）
官方提供的方法，看起来很拧巴，但多一种方法也好。
- 同样先取关联员工和部门表：
select
d.Name as 'Department',
e1.Name as 'Employee',
e1.Salary
from
Employee e1
inner join Department d on e1.DepartmentId = d.Id
- 然后在 where 里做查询，这里需要判断每个员工的当前薪水是否是每个部门里前三高的：
select
d.Name as 'Department',
e1.Name as 'Employee',
e1.Salary
from
Employee e1
inner join Department d on e1.DepartmentId = d.Id
where
(
select
-- 这里 count 时候需要 distinct，因为排序方法是 dense_rank
count(distinct e2.Salary)
from
Employee e2
where
-- 查询同部门下薪水比自己高的同事数量的对应条件
e2.Salary > e1.Salary
and e1.DepartmentId = e2.DepartmentId
-- count 查询结果即为比自己薪资高的位数，即 (count + 1) 为自身的排名
) < 3;
