跳到主要内容

子查询

子查询（Subquery）是将一个查询嵌套在另一个查询中的技术。它是 SQL 中处理复杂逻辑的重要手段，能够解决许多单层查询无法完成的任务。理解子查询的工作原理，是从"会写 SQL"迈向"精通 SQL"的关键一步。

核心概念​

什么是子查询？​

子查询是出现在其他 SQL 语句内部的 SELECT 语句。根据子查询返回结果的不同，可以分为：

子查询的位置​

子查询可以出现在 SQL 语句的多个位置：

位置说明示例
WHERE 子句用于条件过滤WHERE id IN (SELECT ...)
FROM 子句作为派生表FROM (SELECT ...) AS t
SELECT 子句作为计算字段SELECT (SELECT COUNT(*) ...)
HAVING 子句分组后过滤HAVING COUNT(*) > (SELECT ...)
EXISTS 子句存在性检查WHERE EXISTS (SELECT ...)

相关子查询 vs 非相关子查询​

这是理解子查询性能的关键：

非相关子查询：

  • 子查询独立于外层查询
  • 只执行一次
  • 结果用于外层查询
-- 非相关子查询：子查询不依赖外层
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 子查询独立执行，只运行一次

相关子查询：

  • 子查询引用外层查询的列
  • 外层查询的每一行都会执行一次子查询
  • 性能开销较大
-- 相关子查询：子查询引用外层的 e.department_id
SELECT name, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id  -- 引用外层
);
-- 对每个员工都执行一次子查询

IN vs EXISTS​

IN 和 EXISTS 是子查询中最常用的两个操作符，它们有不同的适用场景：

操作符特点适用场景
IN子查询返回的值直接比较子查询结果集较小
EXISTS只判断是否存在，不返回具体值子查询结果集较大、相关子查询
-- IN：先执行子查询，再与外层比较
SELECT name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location = '北京');

-- EXISTS：对每个外层行检查子查询是否有结果
SELECT name FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.department_id AND d.location = '北京');

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 排序。

解题思路​

这道题考查 NOT IN 子查询。

分析过程：

  1. 基础条件：salary < 30000
  2. 经理离职判断：manager_id 不在员工表的 employee_id 列中
  3. NULL 处理：manager_id 不为 NULL（顶级员工没有经理，不算离职）

NOT IN 的陷阱：

如果子查询结果中包含 NULL，NOT IN 会返回意外结果：

-- 如果子查询返回 [1, 2, NULL]
WHERE manager_id NOT IN (1, 2, NULL)
-- 结果永远是空！因为 NULL != NULL 返回 UNKNOWN

所以需要确保子查询排除 NULL：

SELECT employee_id FROM Employees WHERE manager_id IS NOT NULL

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;

代码解析：

  • salary < 30000：薪水条件
  • manager_id IS NOT NULL：排除没有经理的员工
  • NOT IN (SELECT employee_id FROM Employees)：经理不在员工表中

626. 换座位 (Exchange Seats)​

题目描述​

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

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

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

解题思路​

这道题考查 CASE WHEN + 子查询。

交换逻辑：

  • 奇数位学生（id=1,3,5,...）换到 id+1
  • 偶数位学生（id=2,4,6,...）换到 id-1
  • 最后一位如果是奇数位，保持不变

判断最后一位：

id = (SELECT COUNT(*) FROM Seat)  -- id 等于总行数

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;

代码解析：

  • id % 2 = 1 AND id = (SELECT COUNT(*) FROM Seat)：奇数位且是最后一位，保持不变
  • id % 2 = 1 THEN id + 1：奇数位，换到下一位
  • ELSE id - 1：偶数位，换到上一位

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. 最积极用户：按评论次数降序、名字升序，取第一个
  2. 最佳电影：按平均评分降序、电影名升序，取第一个

UNION ALL vs UNION：

  • UNION ALL：保留所有行，包括重复
  • UNION：去除重复行

这里两个查询结果不会重复，用 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);

代码解析：

  • 每个查询用 LIMIT 1 取第一名
  • ORDER BY COUNT(*) DESC, name ASC：先按次数降序，再按名字升序（处理并列）
  • WHERE created_at LIKE '2020-02-%'：筛选 2020 年 2 月

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

题目描述​

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

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

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

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

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

解题思路​

这道题考查 窗口函数的滚动聚合。

预处理步骤：

同一日期可能有多笔消费，需要先按日期汇总：

SELECT visited_on, SUM(amount) AS amount
FROM Customer
GROUP BY visited_on

滚动窗口：

SUM(amount) OVER(ORDER BY visited_on ROWS 6 PRECEDING)
-- 当前行 + 前 6 行 = 7 天的数据

过滤前 6 天：

只有从第 7 天开始才能凑齐 7 天的数据：

WHERE rnk >= 7  -- rnk 是日期排名

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;

代码解析：

  • 内层 GROUP BY visited_on：按日期汇总
  • SUM(SUM(amount)) OVER(...)：先求每天的总额，再用窗口函数求滚动累计
  • ROWS 6 PRECEDING：当前行 + 前 6 行
  • DENSE_RANK()：用于过滤前 6 天

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

题目描述​

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

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

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

解题思路​

这道题考查 UNION ALL 合并数据。

核心思路：

一个人无论是发送者还是接收者，只要产生了一条通过记录，就增加了一名好友。

解决方案：

  1. 将 requester_id 和 accepter_id 合并成一列
  2. 按 ID 分组统计出现次数
  3. 取最大值

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;

代码解析：

  • UNION ALL：合并发送者和接收者（保留重复，因为同一人可能多次出现）
  • COUNT(*)：统计每个人的好友数量
  • 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 有重复（出现过不止一次）
  2. (LAT, LON) 组合是唯一的（只出现一次）

使用子查询实现：

-- 条件1：TIV_2015 有重复
WHERE tiv_2015 IN (SELECT tiv_2015 FROM Insurance GROUP BY tiv_2015 HAVING COUNT(*) > 1)

-- 条件2：(LAT, LON) 唯一
AND (lat, lon) IN (SELECT lat, lon FROM Insurance GROUP BY lat, lon HAVING COUNT(*) = 1)

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)。 公司规定："高额薪水" 是指一个部门内排名在前 三名 的薪水。

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

解题思路​

这道题考查 窗口函数 DENSE_RANK()。

排名函数对比：

函数特点示例（薪水 100, 100, 90, 80）
ROW_NUMBER()连续编号1, 2, 3, 4
RANK()并列跳号1, 1, 3, 4
DENSE_RANK()并列不跳号1, 1, 2, 3

为什么用 DENSE_RANK？

题目说"前三高"，如果两个并列第一，下一个应该是第二高，而不是第三高。这符合 DENSE_RANK 的行为。

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;

代码解析：

  • PARTITION BY departmentId：按部门分区
  • ORDER BY salary DESC：按薪水降序排名
  • DENSE_RANK()：排名函数，并列不跳号
  • WHERE rnk <= 3：取前三名

知识点总结​

子查询类型速查​

类型返回结果使用场景
标量子查询单个值WHERE 条件、SELECT 字段
列子查询一列多行IN、ANY、ALL
行子查询一行多列多列比较
表子查询多行多列FROM 派生表
相关子查询依赖外层逐行判断

常用模式​

-- 1. NOT IN 找差集
WHERE id NOT IN (SELECT id FROM ...)

-- 2. EXISTS 存在性判断
WHERE EXISTS (SELECT 1 FROM ... WHERE ...)

-- 3. 行子查询
WHERE (col1, col2) IN (SELECT col1, col2 FROM ...)

-- 4. UNION ALL 合并数据
SELECT ... UNION ALL SELECT ...

-- 5. 派生表
FROM (SELECT ...) AS t

注意事项​

  1. 相关子查询性能开销大，尽量用 JOIN 替代
  2. NOT IN 子查询要排除 NULL
  3. 使用 UNION ALL 比 UNION 更高效（如果确定无重复）
  4. 窗口函数通常比子查询更简洁高效