MySQL复杂查询实战：从JOIN到窗口函数的完整指南

第5讲，我们正式开始写复杂查询。我给团队做 MySQL 内训的时候，每次讲到这一讲都会先泼一盆冷水：如果你觉得复杂查询就是把几张表 join 在一起，那后面的内容大概率会刷新你的认知。数据操纵语句是日常开发里使用频率最高的一类 SQL，SELECT、INSERT、UPDATE、DELETE 几乎绕不开，而复杂查询则是把多表连接、子查询、分组聚合、窗口函数这些能力叠加到基础语句上，让一句 SQL 能解决原来要写一大段业务代码才能搞定的事。

这一讲的定位是给“会写基础增删改查但没系统梳理过复杂查询”的开发者和学生，也适合正在准备 MySQL 面试、想补齐底层执行逻辑的朋友。学完之后，你至少能回答三个问题：多表 join 到底怎么选内连接和外连接？子查询什么时候该用 EXISTS 而不是 IN？窗口函数为什么能替代大量手动拼写的排名和累计代码？

1. 复杂查询的整体思路：先记住 MySQL 的逻辑执行顺序

1.1 执行顺序决定了你能怎么写 SQL

很多人写复杂查询最大的问题不是语法不会，而是脑子里的执行模型是错的。MySQL 拿到一条 SELECT 语句，并不是按照你书写的顺序去执行的，它有一套固定的逻辑处理顺序。这里我直接给出顺序，建议你拿个小本子抄下来：

FROM 确定数据来源，处理多表连接 WHERE 对 FROM 阶段的结果做行级过滤 GROUP BY 按指定列分组 HAVING 对分组后的结果做过滤 SELECT 投影需要的列，计算表达式 DISTINCT 对结果去重 ORDER BY 排序 LIMIT 分页截断

这个顺序是理解复杂查询的钥匙。为什么这么说？因为它直接解释了两个高频报错：为什么 WHERE 条件里不能用聚合函数？因为执行到 WHERE 时，GROUP BY 还没发生，聚合结果还不存在。为什么 SELECT 里取的别名不能在 WHERE 里用？因为 SELECT 的投影在 WHERE 之后才执行。这两个坑我几乎每次培训都能看到有人踩。

1.2 复杂查询的本质是“分阶段筛选”

把执行顺序刻在脑子里之后，复杂查询就没那么神秘了。你可以把它想象成一条流水线：先从 FROM 把原料搬上工作台，WHERE 是第一次粗筛，GROUP BY 是把原料分筐，HAVING 是对每个筐做二次检查，SELECT 是把你真正要的东西捡出来，ORDER BY 和 LIMIT 是最后整理货架。

很多新手在复杂查询里迷路的根本原因，是试图用“一条 SQL 一次性解决所有逻辑”，一旦业务条件复杂就开始堆条件、堆子查询，最后自己都看不懂。正确的做法是先拆需求：哪部分是行过滤，哪部分是分组统计，哪部分是连接补充字段。拆清楚了，SQL 自然就顺了。

2. 多表连接查询：JOIN 的底层逻辑与实战取舍

2.1 先建一个学生-课程-成绩三表模型

复杂查询不能空谈，我这一讲全部用同一个业务模型来演示：学生、课程、成绩。这也是面试里最常见的表结构设计。先看建表语句：

CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, class_id INT NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE course ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, credit INT NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE score ( student_id INT NOT NULL, course_id INT NOT NULL, score DECIMAL(5,2) NOT NULL, PRIMARY KEY (student_id, course_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

score 表用联合主键 (student_id, course_id)，保证一个学生对一门课只能有一条成绩记录。这里我特意把 score 设计成窄表，因为它未来会被高频关联查询，字段越精简，索引效率越高。

2.2 INNER JOIN 与 LEFT JOIN 的选择逻辑

先看最常见的内连接：查询每个学生的姓名和课程成绩。

SELECT s.name, c.name AS course_name, sc.score FROM score sc JOIN student s ON sc.student_id = s.id JOIN course c ON sc.course_id = c.id;

这里我用的是 INNER JOIN 的简写 JOIN。它的语义是“两表都匹配才返回”。也就是说，如果一个学生没有成绩记录，或者一门课没有学生选，它就不会出现在结果里。对于“我要看真正产生了成绩的数据”这种需求，内连接就是正确答案。

再看一个典型需求：查询所有学生，包括那些还没选任何课的学生。

SELECT s.name, c.name AS course_name, sc.score FROM student s LEFT JOIN score sc ON s.id = sc.student_id LEFT JOIN course c ON sc.course_id = c.id;

注意我把驱动表换成了 student，用 LEFT JOIN 保证 student 表的所有行都保留，匹配不到成绩的地方自动补 NULL。这是内外连接最核心的差异：左边是驱动表，右边是被驱动表，LEFT JOIN 以左表为准，右表没有匹配就填空。日常开发里，查询“主表数据 + 可选附加信息”的场景几乎都是 LEFT JOIN，比如订单列表带用户昵称、文章列表带作者信息，这些都属于主数据必须全量展示的情况。

2.3 ON 和 WHERE 的配合：LEFT JOIN 里的大坑

LEFT JOIN 有一个特别容易翻车的细节：条件放 ON 后面还是 WHERE 后面，结果可能完全不同。看这两个写法：

-- 写法 A：附加条件放在 ON 里 SELECT s.name, sc.score FROM student s LEFT JOIN score sc ON s.id = sc.student_id AND sc.score >= 60; -- 写法 B：附加条件放在 WHERE 里 SELECT s.name, sc.score FROM student s LEFT JOIN score sc ON s.id = sc.student_id WHERE sc.score >= 60;

写法 A 的意图是“左表全保留，右表只匹配及格的成绩”，没及格的学生照样出现，成绩显示为 NULL。写法 B 则先做 LEFT JOIN，再用 WHERE 过滤掉成绩为 NULL 和不及格的行，效果等同于把 LEFT JOIN 变成了 INNER JOIN。这个区别在报表统计里非常致命，很多人写完 SQL 发现数据少了，十有八九就是掉进这个坑。

2.4 自连接：同一个表和自己 Join

还有一个容易懵的场景是自连接。典型需求是查询每个学生的班主任是谁，而班主任也在 student 表里，通过一个 teacher_id 字段指向自己的 id。

SELECT stu.name AS student_name, tea.name AS teacher_name FROM student stu LEFT JOIN student tea ON stu.teacher_id = tea.id;

自连接的关键是必须给表起不同的别名，否则 MySQL 根本分不清你引用的是哪一份。别觉得自连接冷门，员工-经理、分类-父分类、关注-粉丝这类层级关系表，全靠它。

2.5 JOIN 时因重复数据导致爆炸

JOIN 最常见的一个隐藏风险是一对多关联造成结果行数翻倍。比如 score 表里一个学生有多条课程记录，你再 join 一张“学生扩展信息表”，如果扩展信息表里也有多条同名记录，就会出现笛卡尔式的行数膨胀。遇到这种情况，先查一下每张表的粒度，再决定 join 的目标表是不是需要在子查询里先做聚合去重。做报表的人对这个问题应该深有体会：一个 JOIN 把数据放大了一倍，汇总数字全错了，最后只能逐层排查。

3. 子查询：IN、EXISTS 与派生表的正确姿势

3.1 子查询的三种位置

子查询就是嵌套在另一条 SQL 里的 SELECT，按出现位置分成三种：WHERE 子查询、FROM 子查询（也叫派生表）、SELECT 子查询。先看一个 WHERE 子查询的例子：

SELECT name FROM student WHERE id IN ( SELECT DISTINCT student_id FROM score WHERE score < 60 );

这个查询找出“有不及格成绩”的学生名单。子查询先执行，算出一批不合格的 student_id，然后外层 student 表根据这些 id 过滤。逻辑直观，是新手最容易上手的写法。

3.2 EXISTS 与 IN 怎么选

再看 EXISTS 版本：

SELECT name FROM student s WHERE EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id = s.id AND sc.score < 60 );

EXISTS 是关联子查询，它对外层 student 的每一行，去 score 表里做一次存在性检查，只要找到一条满足条件的记录就返回真。注意里面写的是 SELECT 1，不是 SELECT *，因为存在性检查根本不关心查出了什么列，写成 1 既省内存又明确表达意图。

什么时候用 EXISTS 而不是 IN？主要有两个判断点。第一，IN 子查询的结果集如果很大，整个结果集都要在内存里暂存，而 EXISTS 是边遍历外层边探测，通常 EXISTS 在大数据量下表现更稳。第二，IN 遇到 NULL 值会出逻辑问题。如果子查询结果里包含 NULL，IN 判断会变成“既不是真也不是假”，结果集可能意外为空。EXISTS 没有这个问题。当然，现代 MySQL 优化器对 IN 也会做半连接优化，性能差距并没有想象中夸张，但 EXISTS 的语义更安全，我在生产环境里更推荐它。

3.3 FROM 子查询：先查一张临时表再继续查

FROM 子查询的典型场景是“先按明细聚合，再和主表关联”。比如想统计每个学生的平均分，然后找出平均分低于 70 的学生姓名：

SELECT s.name, t.avg_score FROM student s JOIN ( SELECT student_id, AVG(score) AS avg_score FROM score GROUP BY student_id ) t ON s.id = t.student_id WHERE t.avg_score < 70;

派生表 t 就相当于一张只存在于这条 SQL 执行过程中的临时表。这里有个非常实用的经验：派生表一定要起别名，否则 MySQL 会直接报错。另外，派生表的字段尽量在子查询里就把类型和名称定好，外层引用时才不会糊涂。

3.4 关联子查询的逐行执行逻辑

关联子查询是很多人理解上的分水岭。像 3.2 里的 EXISTS 写法，外层每一行都要去执行一次子查询，这叫“相关子查询”。它的威力在于可以引用外层查询的列，但代价是如果优化器没有把它改写为高效的半连接，性能会随外层行数线性下降。

面试里经常问“怎么用一条 SQL 找出每个学生的最高分课程”，这个需求用关联子查询可以写：

SELECT sc1.student_id, sc1.course_id, sc1.score FROM score sc1 WHERE sc1.score = ( SELECT MAX(sc2.score) FROM score sc2 WHERE sc2.student_id = sc1.student_id );

内层子查询对每个学生都求一次最高分，外层再找等于这个最高分的记录。理解了这个逐行执行的逻辑，后面学窗口函数就会轻松很多。

4. 分组聚合：GROUP BY 与 HAVING 的分工

4.1 GROUP BY 的分组本质

GROUP BY 看起来简单，但很多人不知道它的执行逻辑其实是“先分组，再逐组聚合”。在 MySQL 中，一旦使用了 GROUP BY，SELECT 的非聚合列必须出现在 GROUP BY 列表里，否则结果不可控。比如：

SELECT student_id, AVG(score) AS avg_score FROM score GROUP BY student_id;

这条没问题。但如果你写成：

SELECT student_id, course_id, AVG(score) AS avg_score FROM score GROUP BY student_id;

在 MySQL 的 ONLY_FULL_GROUP_BY 模式下会直接报错，因为 course_id 没有被分组也没有被聚合。就算你侥幸关掉了严格模式，查出来的 course_id 也是“这一组里的任意一条”，毫无业务含义。这不仅是语法问题，更是数据正确性问题。

4.2 WHERE 和 HAVING 是怎么分工的

WHERE 在分组之前过滤行，HAVING 在分组之后过滤组。这个区别直接决定你能不能把条件写对。举一个经典需求：统计每个学生的选课数量，只显示选课超过 3 门的学生。

SELECT student_id, COUNT(*) AS course_cnt FROM score GROUP BY student_id HAVING COUNT(*) > 3;

如果你试图在 WHERE 里写 COUNT(*) > 3，MySQL 会直接告诉你“无效使用组函数”，因为执行到 WHERE 的时候还没有分组。反过来，如果条件是“只统计 2024 年秋季的选课记录”，那就必须在 WHERE 里先过滤，因为这是行级条件，提前过滤掉不需要的数据，也能减少后续分组的开销。

4.3 聚合函数里的坑：COUNT 和 SUM 别再踩了

聚合函数看着简单，坑也不少。COUNT(*) 统计行数，COUNT(column) 统计该列非 NULL 值的个数。如果某列大量为 NULL，两个 COUNT 结果可能差很远。SUM 也一样，SUM(column) 会忽略 NULL，但如果整组都是 NULL，SUM 返回 NULL 而不是 0，报表里经常因此出现空白。处理办法是用 IFNULL 或 COALESCE 包一层，比如 SUM(IFNULL(score, 0))。

另一个高频坑是 COUNT(DISTINCT ...) 的性能。去重计数在大表上非常昂贵，如果只是为了看“是否有重复”，不如先用 GROUP BY 加 HAVING COUNT(*) > 1 去定位具体重复行，再做后续处理。

5. 窗口函数：排名与累计计算的高效利器

5.1 窗口函数和 GROUP BY 的本质区别

MySQL 从 8.0 开始支持窗口函数，这绝对是复杂查询里性价比最高的一块内容。窗口函数和 GROUP BY 最大的不同是：GROUP BY 会把多行合并成一行，窗口函数则保留每一行，同时在每一行上额外算出一个“窗口范围内的聚合值”。打个比方，GROUP BY 是把同一个班级的学生成绩汇总成一条班级平均分，窗口函数是每个学生名字旁边都写上“你所在班级的平均分”。

一个最直观的例子，给每个学生的每门成绩加一列“该学生自己的平均分”：

SELECT student_id, course_id, score, AVG(score) OVER (PARTITION BY student_id) AS student_avg FROM score;

PARTITION BY 是“按学生分区”，MySQL 在每个分区内独立计算平均分，但结果不折叠行，每条原始成绩都保留。这就是窗口函数的核心用法。

5.2 ROW_NUMBER、RANK、DENSE_RANK 怎么选

排名是窗口函数最出圈的场景。面试必考三道函数：

SELECT student_id, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_no, RANK() OVER (ORDER BY score DESC) AS rk, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rk FROM score;

三者的区别非常经典：ROW_NUMBER 不管分数是否相同，强行给一个连续排名 1、2、3、4；RANK 相同分数排名相同，但下一个排名会跳号，比如 1、1、3；DENSE_RANK 相同分数排名相同，且下一个排名不跳号，比如 1、1、2。业务上“取前三名”如果用 RANK 可能会取出 4 条，因为并列第三占了两个位置；如果业务要求“必须最多三个人”，就要用 ROW_NUMBER，并列时再按学号或时间做次级排序。

5.3 累计求和与移动平均

窗口函数另一个频繁的应用是累计计算。比如计算截止到每门课的累计成绩：

SELECT student_id, course_id, score, SUM(score) OVER (PARTITION BY student_id ORDER BY course_id) AS running_total FROM score;

ORDER BY 出现在 OVER 子句里时，窗口会被定义为“从分区第一行到当前行”，于是 running_total 每一行都是截至当前课程的累计值和。这种逻辑以前要用变量或者多层子查询硬写，现在一句 SQL 就解决。同理，移动平均只要把窗口范围改成 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 就能算出“近 3 条数据的平均值”。

6. 用 EXPLAIN 给复杂查询做体检

6.1 看懂执行计划的核心字段

写复杂查询，不会用 EXPLAIN 等于开车不看仪表盘。你只需要在 SQL 前面加一个 EXPLAIN 关键词，MySQL 就会告诉你这条查询准备怎么执行：

EXPLAIN SELECT s.name, c.name, sc.score FROM score sc JOIN student s ON sc.student_id = s.id JOIN course c ON sc.course_id = c.id WHERE sc.score < 60;

重点关注几个字段：type 表示访问类型，从好到差大致是 system > const > eq_ref > ref > range > index > ALL，看到 ALL 就要警惕全表扫描；key 表示实际使用的索引；rows 是预估扫描行数，数字越大说明成本越高；Extra 里如果出现 Using filesort 或 Using temporary，说明排序或分组没法用索引完成，数据量一大性能就会崩。

6.2 联合索引的顺序不能乱

性能优化里，索引设计和 SQL 写法的配合特别重要。比如这张成绩表经常按“课程 + 分数”过滤，建一个联合索引 (course_id, score) 就很合理。但要注意，联合索引有“最左前缀”原则，查询条件里如果只写 score 而不带 course_id，这个索引就用不上。所以建索引之前，先梳理你的 WHERE、JOIN、ORDER BY 到底涉及哪些列，把最常用于等值筛选的列放最前面。

6.3 三个常见的性能杀手

我总结过复杂查询最常见的三个性能杀手。第一，SELECT *。复杂查询结果集往往很大，只取需要的列既能减少网络传输，也能让优化器更容易覆盖索引。第二，在索引列上做函数运算，比如 WHERE DATE(create_time) = '2024-01-01'，索引会失效，正确写法是 WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'。第三，深分页，LIMIT 100000, 20 这种写法要扫描前面十万行才能丢掉，建议改成基于主键或唯一键的游标分页。

7. 综合实战：学生成绩排行与统计一次搞定

7.1 需求拆解

这一节的实战需求很典型：按班级统计每位学生的总成绩排名，同时在结果里展示学生的选课门数、平均分，以及该学生每门课是否高于课程平均分。这个需求如果不拆解，很容易写得一团糟。我把它拆成三层：第一层要按学生汇总总成绩和选课数；第二层要按总成绩在班级内排名；第三层要关联每门课的平均分做对比。

7.2 逐步实现

先做第一层和第二层，用窗口函数直接一步到位：

SELECT s.id AS student_id, s.name, s.class_id, COUNT(sc.course_id) AS course_cnt, SUM(sc.score) AS total_score, RANK() OVER (PARTITION BY s.class_id ORDER BY SUM(sc.score) DESC) AS class_rank FROM student s LEFT JOIN score sc ON s.id = sc.student_id GROUP BY s.id, s.name, s.class_id;

注意一个细节：窗口函数 OVER 里的 ORDER BY SUM(sc.score) 这种写法是允许的，因为它是在分组聚合之后才计算的窗口。先把学生维度的数据算出来，再用窗口函数在同一批结果上打排名，不需要额外子查询。

第三层更复杂一点，先用派生表算出每门课的平均分，再关联到成绩明细，最后用 CASE WHEN 判断学生成绩是否高于平均分：

SELECT sc.student_id, sc.course_id, sc.score, CASE WHEN sc.score > c.avg_score THEN '高于平均' WHEN sc.score = c.avg_score THEN '等于平均' ELSE '低于平均' END AS compare_result FROM score sc JOIN ( SELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id ) c ON sc.course_id = c.course_id;

7.3 把多层逻辑串起来

如果业务上要求把上面的学生排名和课程对比合成一张宽表，我的建议是不要强行用一个超大 SQL 一次写完。更稳妥的做法是分别查出学生维度和成绩明细维度，在应用层按 student_id 关联。这样每条 SQL 的职责清晰，执行计划容易把控，排查问题也方便。很多复杂查询翻车，不是 SQL 语法不会，而是过度追求“一条 SQL 天下无敌”，最后谁都维护不了。

8. 复杂查询常见问题速查表

最后整理一份我平时答疑时最常遇到的排查清单，建议截图收藏：

做复杂查询这么多年，我个人最大的体会是：SQL 能力的瓶颈不在语法记忆，而在“能不能在大脑里把执行顺序跑一遍”。每一条复杂查询你都要能回答——哪些条件在 WHERE 阶段生效，哪些在 HAVING 阶段生效，哪个子查询先执行，哪个窗口后计算。把这一讲里的执行顺序、JOIN 语义、子查询逻辑、窗口函数边界都吃透，再去面对面试题或者生产报表，你会发现大多数“难题”其实只是几个基础能力的叠加。最后再分享一个建议：写完每条复杂查询，都掏出一个最小数据集亲手执行一遍，别只盯着结果对不对，还要看 EXPLAIN 的扫描行数变化。这个习惯，比看十篇教程都管用。