跳到主要内容

子查询

子查询涉及 IN, EXISTS, ANY/ALL 以及相关子查询的逻辑嵌套，是解决复杂筛选的核心。

1978. 上级经理已离职的公司员工 (Employees Whose Manager Left the Company)​

题目描述​

Employees 表包含公司员工的信息：

  • employee_id: 员工 ID，主键。
  • name: 姓名。
  • manager_id: 上级经理的 ID。
  • salary: 薪水。

请编写一个解决方案，找出所有 薪水严格小于 30000 且 上级经理已离职 的员工。当一个员工有一个关联的 manager_id，但该 ID 已不再出现在 Employees 表的 employee_id 列中时，即认为该经理已经离职。 结果请按 employee_id 排序。

解题思路​

多维子查询筛选。

  1. 基础过滤：salary < 30000 且 manager_id 不为 NULL（因为顶级经理没有上级也算离职是不合理的）。
  2. 离职判断：使用 manager_id NOT IN (SELECT employee_id FROM Employees) 来锁定那些指向了不存在的 ID 的记录。

SQL 实现 (MySQL)​

SELECT employee_id 
FROM Employees 
WHERE salary < 30000 
AND manager_id IS NOT NULL 
AND manager_id NOT IN (SELECT employee_id FROM Employees) 
ORDER BY employee_id;

626. 换座位 (Exchange Seats)​

题目描述​

Seat 表记录了每名学生及其分配的座位 ID：

  • id: 座位 ID，连续的整数。
  • student: 学生姓名。

请编写一个解决方案，交换相邻两名学生的座位。如果学生人数是奇数，则最后一名学生的座位不进行交换。

解题思路​

分段逻辑变换。

  1. 坐标偏移：通常情况下，奇数位的学生移动到 id+1，偶数位的学生移动到 id-1。
  2. 尾项处理：如果全表人数是奇数，则最后一项（其 id 等于全表行数）应保持原样，不参与 +1 操作。
  3. 计算总行数：使用子查询 (SELECT COUNT(*) FROM Seat) 动态获取总数。

SQL 实现 (MySQL)​

SELECT (
    CASE 
        WHEN id % 2 = 1 AND id = (SELECT COUNT(*) FROM Seat) THEN id 
        WHEN id % 2 = 1 THEN id + 1 
        ELSE id - 1 
    END
) AS id, student 
FROM Seat 
ORDER BY id;

1341. 电影评分 (Movie Rating)​

题目描述​

你有三张表：

  • Movies 表：包含电影信息。
    • movie_id: 电影 ID，主键。
    • title: 电影名称。
  • Users 表：包含用户信息。
    • user_id: 用户 ID，主键。
    • name: 用户名。
  • MovieRating 表：记录了用户对电影的评分。
    • movie_id: 电影 ID。
    • user_id: 用户 ID。
    • rating: 评分。
    • created_at: 评分日期。

请编写一个解决方案，输出两部分结果：

  1. 最积极用户：找出评论电影次数最多的用户名。如果有多个用户并列，则返回字典序较小的那一个。
  2. 最佳电影：找出在 2020 年 2 月 平均评分最高的电影名称。如果有多个电影并列，则返回字典序较小的那一个。

解题思路​

UNION ALL 结果拼接。

  1. 各司其职：分别为两个子需求编写完整的 SELECT 语句，每个语句内部使用 GROUP BY, ORDER BY（双重排序）并配合 LIMIT 1。
  2. 合而为一：使用 UNION ALL 将两个只有一行的结果合并在一起。

SQL 实现 (MySQL)​

(SELECT u.name AS results 
 FROM MovieRating mr JOIN Users u USING(user_id) 
 GROUP BY user_id 
 ORDER BY COUNT(*) DESC, name ASC LIMIT 1) 
UNION ALL 
(SELECT m.title AS results 
 FROM MovieRating mr JOIN Movies m USING(movie_id) 
 WHERE created_at LIKE '2020-02-%' 
 GROUP BY movie_id 
 ORDER BY AVG(rating) DESC, title ASC LIMIT 1);

1321. 餐馆营业额变化增长 (Restaurant Growth)​

题目描述​

Customer 表记录了顾客在不同日期的消费额：

  • customer_id: 顾客 ID。
  • visited_on: 访问日期。
  • amount: 消费金额。

你需要计算：从每一个可能的日期开始，过去 7 天内（包含当天）的累计消费额和平均消费额。

  • 金额总和 (amount)：7 天的总额。
  • 平均额 (average_amount)：7 天的总额除以 7，保留两位小数。

注意：结果应从第 7 天（即能够凑齐过去 7 天数据的日期）开始展示。结果按日期升序排列。

解题思路​

窗口函数滚动聚合。

  1. 预处理：由于同一天可能有多笔交易，先按天合并总额 SUM(amount) GROUP BY visited_on。
  2. 滑动窗口：使用 SUM(...) OVER(ORDER BY visited_on ROWS 6 PRECEDING) 计算当前日期及前 6 天的总和。
  3. 起始过滤：由于窗口函数会自动处理不足 7 天的情况，我们需要根据日期排名或 ID 过滤掉前 6 条记录。

SQL 实现 (MySQL)​

SELECT visited_on, amount, ROUND(amount / 7, 2) AS average_amount 
FROM (
    SELECT visited_on, SUM(SUM(amount)) OVER(ORDER BY visited_on ROWS 6 PRECEDING) AS amount, 
           DENSE_RANK() OVER(ORDER BY visited_on) AS rnk 
    FROM Customer 
    GROUP BY visited_on
) t 
WHERE rnk >= 7;

602. 好友申请 II ：谁有最多的好友 (Friend Requests II: Who Has the Most Friends)​

题目描述​

RequestAccepted 表保存了通过的好友申请记录：

  • requester_id: 发送者 ID。
  • accepter_id: 接收者 ID。
  • accept_date: 通过日期。

请编写一个解决方案，找出 好友数量最多 的人 ID 及其对应的好友总数。题目保证仅有一人拥有最多的好友。

解题思路​

双列合并与分组计数。

  1. 视角转换：一个人无论是发送者还是接收者，只要产生了一条通过记录，就增加了一名好友。
  2. 堆叠数据：使用 UNION ALL 将两列 ID 合并成一个单列集合。
  3. 聚合：按 ID 分组统计出现次数，取最大值即可。

SQL 实现 (MySQL)​

SELECT id, COUNT(*) AS num FROM (
    SELECT requester_id AS id FROM RequestAccepted 
    UNION ALL 
    SELECT accepter_id AS id FROM RequestAccepted
) t 
GROUP BY id 
ORDER BY num DESC 
LIMIT 1;

585. 2016年的投资 (Investments in 2016)​

题目描述​

Insurance 表保存了保险单的信息：

  • PID: 投保人 ID。
  • TIV_2015: 2015 年的总投资。
  • TIV_2016: 2016 年的总投资。
  • LAT, LON: 经纬度位置信息。

请查询所有满足以下 两个条件 的投保人的 2016 年总投资 (TIV_2016) 之和（保留两位小数）：

  1. 至少有一个其他投保人的 TIV_2015 与其相同。
  2. 该投保人的地理位置 (LAT, LON) 必须是唯一的（即没有任何其他投保人位于同一地点）。

解题思路​

复合子查询过滤。

  1. 条件一：在主表记录中筛选出那些 TIV_2015 在全表中重复出现次数大于 1 的行。
  2. 条件二：同时筛选出那些 (LAT, LON) 组合在全表中只出现过 1 次的行。
  3. 加和：对满足双重条件的行进行求和。可以使用窗口函数或嵌套子查询来实现。

SQL 实现 (MySQL)​

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. 部门工资前三高的所有员工 (Department Top Three Salaries)​

题目描述​

你有两张表：Employee (包含 id, name, salary, departmentId) 和 Department (包含 id, name)。 公司规定：“高额薪水” 是指一个部门内排名在前 三名 的薪水。注意，并列排名的薪水也应该被考虑进去（例如，如果有两个人的薪水并列第一，接下来的高薪水排名应该是第三？不，按照 LeetCode 规范通常是指前三档不同的薪水，即 DENSE_RANK 逻辑）。

请查询每个部门中哪些员工属于高额薪水阶层（即该部门内排名前三档的薪水获得者）。

解题思路​

窗口函数分区排名。

  1. 分区排名：使用 DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) 为每个部门的员工按工资降序打分。
  2. 什么是 DENSE_RANK：如果两个员工薪水都是 1000 并列第一，那么下一个薪水 900 的员工排名为 2。这正好对应“前三高”的实际意义。
  3. 过滤结果：在外层查询中保留排名小于等于 3 的员工，并连接部门表获取其名称。

SQL 实现 (MySQL)​

SELECT d.name AS Department, e.name AS Employee, salary AS Salary 
FROM (
    SELECT name, salary, departmentId, DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS rnk 
    FROM Employee
) e JOIN Department d ON e.departmentId = d.id 
WHERE rnk <= 3;

(注：DENSE_RANK 处理并列情况：例如若有两人工资第一，接下来第三个人的名次是第二)。 (注：LeetCode 中的困难题目逻辑通常与此题类似)。