大家好！👋
已经有一段时间没在这里发帖了。过去几天里，我一直在从头复习 SQL——不仅仅是解题，而是理解 为什么 不同的 SQL 概念存在以及 何时 应该使用它们。
一个让我（以及许多初学者）感到困惑的话题是 关联子查询。
你可能见过这样的查询：
SELECT ...
FROM table1 t1
WHERE EXISTS (
SELECT *
FROM table2 t2
WHERE t2.id = t1.id
);
Enter fullscreen mode Exit fullscreen mode
并想知道：
内层查询是如何访问外层查询的列的？
内层查询何时执行？
这与普通子查询有什么不同？
在这篇文章中，我们将通过解决 LeetCode 上的一个真实 SQL 问题来回答所有这些问题。
到最后，你将理解：
- ✅ 什么是关联子查询
- ✅ 它们在内部是如何工作的
- ✅ SQL 的逻辑执行顺序
- ✅ 何时使用它们
- ✅ 它们与窗口函数的比较
让我们开始吧！
前置知识
本文假设你已了解：
- 基本的
SELECT语句WHEREGROUP BY- 聚合函数（
MIN、MAX、COUNT等）如果你对这些已经熟悉，那么你已经准备好学习关联子查询了。
目录
- 为什么关联子查询如此令人困惑？
- 从零开始理解关联子查询
- 关联子查询 vs 非关联子查询
- SQL 的逻辑执行顺序（以及关联子查询的定位）
- 以 LeetCode 3421 作为贯穿全文的示例
- 在编写 SQL 之前...
- 我的完整带注释解决方案
- 使用真实数据逐步演练查询
- 性能讨论：关联子查询 vs 窗口函数
- 结论与练习题
1. 为什么关联子查询如此令人困惑？
当我第一次开始学习 SQL 时，关联子查询感觉就像魔法一样。
类似这样的问题不断浮现在脑海中：
- 内层查询如何访问外层查询的列？
- 内层查询何时执行？
- 它是运行一次还是多次？
- 为什么我不能独立执行它？
如果你也问过自己这些问题，别担心——到本文结束时，你将得到所有答案。
什么是子查询？
子查询只是嵌套在另一个 SQL 语句中的 SELECT 语句。没什么可怕的：
SELECT name, salary
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
Enter fullscreen mode Exit fullscreen mode
这里，内层查询 (SELECT AVG(salary) FROM Employees) 运行 一次，生成一个数字（比如 55000），然后外层查询使用这个数字。内层查询不在乎外层查询当前正在查看哪一行。这是一个 非关联（或“简单”）子查询——它是完全独立的，可以独立运行。
是什么让子查询变成“关联”的？
💡 核心思想
关联子查询依赖于外层查询的值。
因此，它无法独立执行——它会为外层查询的每一行重新执行一次，每次使用该行的值。
SELECT e.name, e.salary, e.department_id
FROM Employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM Employees e2
WHERE e2.department_id = e.department_id -- 👈 引用外层行 `e`
);
Enter fullscreen mode Exit fullscreen mode
单独阅读这个内层查询：
SELECT AVG(salary) FROM Employees e2 WHERE e2.department_id = e.department_id
Enter fullscreen mode Exit fullscreen mode
e.department_id 在这个子查询自己的 FROM Employees e2 中不存在——它是从 外层 查询借来的。这就是定义性特征。从概念上想，这就像一个 带参数的函数：
find_avg_salary_for(department_id):
return AVG(salary) WHERE department_id = department_id
Enter fullscreen mode Exit fullscreen mode
而 SQL 会为外层查询触及的每一行调用这个“函数”一次，传入该行的 department_id。这就是整个概念。其余一切都只是这个想法之上的装饰。
一个简单的心理模型
把外层查询想象成一个 for 循环，而关联子查询是运行在该循环内部的代码，使用循环变量：
# 这不是真实的 SQL——只是一个心理模型
for row in Employees:
dept_avg = AVG(salary WHERE department_id == row.department_id)
if row.salary > dept_avg:
emit(row.name, row.salary, row.department_id)
Enter fullscreen mode Exit fullscreen mode
这个心理模型正是为什么关联子查询可能会变得昂贵——因为这确实接近于数据库在无法优化掉它时最终执行它的方式（更多内容请参阅性能部分）。
小结
关联子查询为外层查询产生的每一行（或每一组）执行一次，使用该行的值作为其输入。
| 非关联子查询 | 关联子查询 | |
|---|---|---|
| 引用外层查询？ | ❌ 否 | ✅ 是 |
| 可以独立运行？ | ✅ 可以 | ❌ 不可以 |
| 执行频率 | 总共一次 | 每外层行一次（概念上） |
| 典型用途 | 与全局值比较（例如整体平均值） | 与该行所在组的值比较（例如该部门的平均值、该学生的最高日期） |
非关联示例——“薪资高于公司平均值的员工”：
SELECT name FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
Enter fullscreen mode Exit fullscreen mode
关联示例——“薪资高于自己部门平均值的员工”：
SELECT e.name FROM Employees e
WHERE e.salary > (
SELECT AVG(salary) FROM Employees e2 WHERE e2.department_id = e.department_id
);
Enter fullscreen mode Exit fullscreen mode
同样的形式，一个关键区别：WHERE e2.department_id = e.department_id 这一行。这一行就是“全局”与“每行范围”的全部区别。
小结
如果子查询可以独立运行并返回合理结果，则它是非关联的。如果它需要外层行的值才能有意义，则它是关联的。
4. SQL 的逻辑执行顺序（以及子查询的定位）
这让很多人困惑，所以让我们把它弄清楚。SELECT 语句是自上而下书写的（SELECT → FROM → WHERE → GROUP BY → ...），但它并不按照这个顺序执行。MySQL（以及大多数 SQL 引擎）实际评估的逻辑顺序是：
① FROM （以及 JOINs）——构建工作行集
↓
② WHERE ——筛选单个行
↓
③ GROUP BY ——将行分桶成组
↓
④ HAVING ——筛选组
↓
⑤ SELECT ——计算输出列
（这里运行 SELECT 列表中的子查询）
↓
⑥ ORDER BY ——对最终结果排序
↓
⑦ LIMIT ——修剪结果
Enter fullscreen mode Exit fullscreen mode
位于 SELECT 列表中 的关联子查询（比如我下面解决方案中的那些）在 步骤 ⑤ 被评估——对于通过 FROM → WHERE → GROUP BY → HAVING 的每一行/组。这对我们的问题来说是关键洞见：
💡 核心思想
当
SELECT中的关联子查询运行时，MySQL 已经确定了存在哪些(student_id, subject)组（多亏了GROUP BY）。然后子查询被询问，每个组一次：“对于这个特定的学生和科目，第一个分数是多少，最新的分数是多少？”
就是这样。整个查询实际上只是：1) 按学生+科目分组，2) 为每个组询问两个附带问题。
小结
SELECT 列表中的关联子查询在步骤 ⑤ 运行——对于已经通过筛选和分组的每一组运行一次。
5. 以 LeetCode 3421 作为贯穿全文的示例：找出进步的学生
表：Scores
| 列名 | 类型 |
|---|---|
| student_id | int |
| subject | varchar |
| score | int |
| exam_date | varchar |
(student_id, subject, exam_date) 是主键。每一行是一个学生在一门科目的一次考试日期上的分数。score 介于 0 到 100 之间。
任务：找出进步的学生。如果一个学生在给定科目上满足以下两个条件，则被视为进步：
- 他们在该科目至少在两个不同日期参加了考试
- 他们在该科目的最新分数高于他们的第一次分数
返回 student_id, subject, first_score, latest_score，按 student_id、subject 升序排列。
示例输入：
| student_id | subject | score | exam_date |
|---|---|---|---|
| 101 | Math | 70 | 2023-01-15 |
| 101 | Math | 85 | 2023-02-15 |
| 101 | Physics | 65 | 2023-01-15 |
| 101 | Physics | 60 | 2023-02-15 |
| 102 | Math | 80 | 2023-01-15 |
| 102 | Math | 85 | 2023-02-15 |
| 103 | Math | 90 | 2023-01-15 |
| 104 | Physics | 75 | 2023-01-15 |
| 104 | Physics | 85 | 2023-02-15 |
预期输出：
| student_id | subject | first_score | latest_score |
|---|---|---|---|
| 101 | Math | 70 | 85 |
| 102 | Math | 80 | 85 |
| 104 | Physics | 75 | 85 |
注意被过滤掉的内容：
- 101 / Physics——参加了两次，但分数下降了（65 → 60）。不算进步。
- 103 / Math——只参加了一次考试。不满足“至少两个不同日期”。
陈述简单，但请注意核心难点：对于每个 (student_id, subject) 对，你需要回溯到同一张表来找到该特定组的最小日期和最大日期，然后找到与这些日期关联的分数。这种“回溯到同一张表，但范围限定为当前行的组”正是关联子查询的设计用途。
6. 在编写 SQL 之前...
让我们暂时忘记 SQL。
想象一下你在用 C++、Java 或 Python 编写这个。
你会怎么做？可能是这样的：
for each student
for each subject
earliest_exam
latest_exam
first_score
latest_score
if latest_score > first_score
print()
Enter fullscreen mode Exit fullscreen mode
SQL 只不过是表达这个算法的另一种方式。GROUP BY 给你 for each student / for each subject 部分，而关联子查询则给你 earliest_exam、latest_exam、first_score 和 latest_score 的查找。把这个循环记在脑子里——它会让下面的查询感觉不那么抽象。
7. 我的带注释解决方案
完整查询
WITH t AS (
SELECT
student_id,
subject,
-- 关联子查询 #1：获取与该组最早考试日期关联的分数
(SELECT score
FROM Scores s
WHERE s.student_id = t.student_id
AND s.subject = t.subject
AND s.exam_date = (
-- 嵌套关联子查询：为该学生+科目查找 MIN(exam_date)
SELECT MIN(exam_date)
FROM Scores ss
WHERE ss.student_id = t.student_id
AND ss.subject = t.subject
)
) AS first_score,
-- 关联子查询 #2：获取与该组最新考试日期关联的分数
(SELECT score
FROM Scores s
WHERE s.student_id = t.student_id
AND s.subject = t.subject
AND s.exam_date = (
-- 嵌套关联子查询：为该学生+科目查找 MAX(exam_date)
SELECT MAX(exam_date)
FROM Scores ss
WHERE ss.student_id = t.student_id
AND ss.subject = t.subject
)
) AS latest_score
FROM Scores t
GROUP BY student_id, subject
)
SELECT *
FROM t
WHERE latest_score > first_score
ORDER BY student_id ASC, subject ASC;
Enter fullscreen mode Exit fullscreen mode
每个部分的作用
-
FROM Scores t GROUP BY student_id, subject——将原始考试行压缩为每个(student_id, subject)组合一行。这是我们要评估的“候选集”。 -
first_score——一个嵌套在另一个关联子查询内部的关联子查询。内层的（MIN(exam_date)，范围限定为WHERE ss.student_id = t.student_id AND ss.subject = t.subject）找到限定于当前组t的最早日期。然后外层查询获取该确切日期、确切学生和科目的score。 -
latest_score——同样的想法，但使用MAX(exam_date)而不是MIN(exam_date)。 -
“至少两个不同日期”的条件是隐式处理的：如果一个学生/科目只有一个考试日期，那么
first_score和latest_score最终会是相同的值（同一日期 → 同一分数），因此latest_score > first_score为false，该行被外层WHERE自然过滤掉。这是逻辑的一个不错副作用——不需要额外的HAVING COUNT(DISTINCT exam_date) >= 2。 -
最终的
WHERE latest_score > first_score——实际的“他们是否进步”检查。 -
ORDER BY student_id, subject——匹配问题要求的输出排序。
创建关联的那一行
一切都取决于嵌套子查询中的这对条件：
WHERE
ss.student_id = t.student_id
AND ss.subject = t.subject
Enter fullscreen mode Exit fullscreen mode
这两个条件就是全部的魔法。它们将内层查询的 MIN/MAX 计算绑定到外层查询 t 的这一特定行，而不是在整个表上计算 MIN/MAX。移除它们，子查询将不再是关联的——它只会变成“所有学生和科目的最早日期”，这根本不是我们想要的。
小结
嵌套的 MIN(exam_date) / MAX(exam_date) 子查询之所以是关联的，是因为它们被外层组的 student_id 和 subject 过滤——这将它们限定为“这个学生，这个科目”而不是整个表。
8. 使用真实数据逐步演练查询
让我们使用示例数据跟踪 学生 101，科目 Math：
| exam_date | score |
|---|---|
| 2023-01-15 | 70 |
| 2023-02-15 | 85 |
外层查询
----------------------------------
student_id = 101
subject = 'Math'
----------------------------------
Enter fullscreen mode Exit fullscreen mode
│
▼
关联子查询
----------------------------------
Find MIN(exam_date)
WHERE
student_id = 101
AND subject = 'Math'
----------------------------------
Enter fullscreen mode Exit fullscreen mode
│
▼
2023-01-15
│
▼
查找 exam_date = 2023-01-15 时的 score
│
▼
first_score = 70
latest_score 分支遵循完全相同的路径，只是将 MIN(exam_date) 替换为 MAX(exam_date)，解析为 2023-02-15 → score = 85。
综合起来，逐步：
-
GROUP BY生成组(101, Math)。 first_score的内层子查询：(101, Math)的MIN(exam_date)→2023-01-15。first_score的外层子查询：查找exam_date = 2023-01-15且 student=101, subject=Math 时的分数 →70。latest_score的内层子查询：(101, Math)的MAX(exam_date)→2023-02-15。latest_score的外层子查询：查找exam_date = 2023-02-15时的分数 →85。- 到目前为止的行：
(101, Math, 70, 85)。 - 最终过滤：是
85 > 70吗？是 → 行保留。
现在跟踪 学生 103，科目 Math（只有一次考试）：
| exam_date | score |
|---|---|
| 2023-01-15 | 90 |
- 组
(103, Math)。 -
MIN(exam_date)→2023-01-15→first_score = 90。 -
MAX(exam_date)→2023-01-15（同一日期，只有一行）→latest_score = 90。 - 最终过滤：是
90 > 90吗？否 → 行被丢弃。这正是“至少两次考试日期”规则如何免费执行的。
以及 学生 101，科目 Physics（分数从 65 下降到 60）：
-
first_score = 65，latest_score = 60。 - 是
60 > 65吗？否 → 被丢弃。
这就是整个算法，手动跟踪。
小结
每个组的 first_score 和 latest_score 都是通过“询问”相同的两步问题独立解析的：找到边界日期，然后找到该日期的分数。
9. 性能讨论：子查询 vs 窗口函数
MySQL 8.0+ 为我们提供了窗口函数，它可以更高效、更可读地表达“每组的第一个和最后一个值”：
窗口函数版本
WITH ranked AS (
SELECT
student_id,
subject,
score,
exam_date,
FIRST_VALUE(score) OVER (
PARTITION BY student_id, subject ORDER BY exam_date ASC
) AS first_score,
FIRST_VALUE(score) OVER (
PARTITION BY student_id, subject ORDER BY exam_date DESC
) AS latest_score,
COUNT(*) OVER (PARTITION BY student_id, subject) AS exam_count
FROM Scores
)
SELECT DISTINCT student_id, subject, first_score, latest_score
FROM ranked
WHERE exam_count >= 2 AND latest_score > first_score
ORDER BY student_id, subject;
Enter fullscreen mode Exit fullscreen mode
💡 核心思想
窗口函数通常要求引擎一次对数据进行排序/分区，然后在单次遍历中为每个分区计算所有值。相比之下，我的关联子查询版本会为每个组重新运行
MIN/MAX/查找子查询——在最坏的情况下（或没有可靠索引时），这些内层查找可能会退化为每个组重新扫描相关行，而不是单一的统一遍历。
不过——关于我原始解决方案的一个公平说明：由于 GROUP BY，关联子查询只运行每个不同的 (student_id, subject) 对一次，而不是每个原始行一次。使用 (student_id, subject, exam_date) 上的适当复合索引，MySQL 可以几乎立即满足每个 MIN/MAX 查找和 score 的点查找，因此在实际数据集大小上，这个查询在实践中表现相当好——它不是人们有时认为关联子查询总是的 O(n²) 最坏情况。窗口函数版本仍然是表达它的更“现代 SQL”方式，并且扩展性更可预测，但一旦建立了索引，差异往往比人们预期的要小。
经验法则：
- 当你需要表达“对于每一行/组，询问一个关于相关范围的有针对性的问题”时，请使用关联子查询——它们非常适合表达这种可读性。
- 当你在整个分区数据集上计算这种 first/last/rank/running-total 逻辑时，请使用窗口函数——它们通常扩展性更好，并且避免每个组的重新执行。
- 在假设其中任何一个是“快速的”之前，始终在你的实际数据上检查
EXPLAIN——数据大小、索引和 MySQL 版本比教科书的复杂度故事更重要。
小结
关联子查询在可读性和有针对性的每组查找方面表现出色；窗口函数在规模方面表现出色，因为它们避免了反复运行相同的计算。
10. 结论与进一步练习
从这整篇文章中要带走的核心思想小到可以塞进一句话：
关联子查询只是一个迷你查询，它一次从外层行获得一个值，并回答针对该值的范围限定问题。
一旦理解了这一点，大多数“困难”的关联子查询问题就不再困难了——它们只是变成了“你需要为每个行/组询问什么迷你问题，以及它需要外层查询的什么值？”
如果你想进一步锻炼这种能力，这里有几个直接依赖相同技能的问题：
- 第二高薪资（经典的关联子查询热身）
- 部门前三薪资
- 排名分数
- 连续数字
- 薪资高于经理的员工
尝试用我在这里做的方式解决每个问题两次：一次使用关联子查询（或窗口函数），一次像在纸上循环一样手动跟踪。循环跟踪实际上是建立直觉的东西——SQL 语法只是之后对这种直觉的编码。
最终思考
关联子查询通常被认为是 SQL 概念中最棘手的一个——不是因为语法困难，而是因为很容易迷失哪个查询正在执行以及引用了哪一行。
一旦你意识到内层查询只是从外层查询的当前行“借用”值，这个概念就会变得更加直观。
我希望这篇文章能让这个心理模型更清晰一些。
祝学习愉快！🚀
0 Comments
Log in to join the conversation.No comments yet. Be the first to share your thoughts.