高级题难的不是语法，是「同一件事有好几种写法，选错了结果就不对」。
所以这一篇的重点在「为什么不能那样写」。
窗口函数不能进 where——
要先把结果落成视图或子查询，外面再筛。 这是九道题里最常撞的一条。
NULL 会从四个方向咬人：
缺考的行不存在、not in 遇 NULL 整体失效、lag 第一行没有上一行、
left join 匹配不上。
这一篇九道题，跑在同一个库上，
但第 4 题会删掉一条成绩造一个「缺考」——
所以从第 4 题开始，英语只剩五条记录。 后面几题的输出能看出这个变化，不是数据错了。
三种新东西在这一篇第一次成规模出现：窗口函数（rank／sum over／lag）、
反连接（left join + is null）、
行转列（max(case when … end)）。 它们都不是 MySQL 独有的，SQLite 一样跑。
一、高级题到底难在哪
基础题的答案通常只有一行一个数， 对错一眼能看出来。 高级题的答案经常是「一组」，而且同一题有不止一种合理答案。 所以难点变成两个：一是「并列怎么算」这种需求层面的决定， 二是「同一件事几种写法，哪种会出错」。 这一篇九道题，每题都尽量给出一条错写法 + 它的结果。 还有一个共同点： 答案不止一行的时候最容易出错。 「每门课的第一名」如果两个人并列，你要一行还是两行？ 这个决定必须写进 SQL 里，不能靠数据凑巧。
二、答案不止一行的时候
第 1 题「每个学生最高分那门课」，两种写法都对。
派生表 join：先算出每个学生的最高分，再拿它去匹配原始行。 写法长一点，但逻辑最直白。
行值 in：
(stu_id, score) in (子查询) 把两个列一起比。 写法短，SQLite 和 MySQL 都支持，
但有些老版本 MySQL 对行值 in 支持不好。
两种写法跑出来都是六行，一字不差。
但如果同一个人有两门课同分且都是最高，两种写法都会给出两行——
这就是「答案不止一行」的第一个坑。 想只留一门，得再加排序和 limit，或者用窗口函数。
第 2 题用 rank() 解决这个问题：rank() over (partition by 课程 order by 分数 desc)
的意思是「按课程分组，组内按分数降序排，给每行一个名次」。
三个排名函数的差别只在「并列时怎么算」：rank() 给 1、1、3（跳号）；
dense_rank() 给 1、1、2（不跳号）；
row_number() 硬排出 1、2、3、4…（并列那两人谁前谁后看运气）。
Demo 里专门造了一次并列来对比：把张伟的数学从 92 改成 96，和孙悦并列第一，
三个函数的结果立刻分道扬镳。 这条对比不做，光看文字是记不住的。
第 3 题把条件从 = 1 改成 <= 2，
直接从上一题的视图里筛。 这就是「先把窗口函数落成视图」的好处——同一份名次，能反复用。
三、NULL 从四个方向咬人
第 4 题是「英语缺考的人」，正确写法是反连接。
left join 保留左表所有行，匹配不上的右表列全是 NULL，
所以「匹配不上」就等于「右表主键是 NULL」。
这里有个非常容易写错的地方：
and sc.course_id = 3 必须写在 on 里，不能写在 where 里。
写进 where 会把左连接退化成内连接——where 先滤掉 NULL 那行，剩下的全是能匹配上的。
Demo 里两种写法都跑了：
on 里那版给出「周杰」一个人，where 里那版给出「张伟、李娜、王强、赵敏、孙悦」五个人。
五个都是「有英语成绩」的，正好和需求相反，而且一句错都不报。
第 5 题是 not in 的 NULL 陷阱。 not in 的写法看着更直白，在这道题上结果也对——
但它有个致命前提：子查询那一列不能有 NULL。
这个案例库因为主键约束，stu_id 压根插不进 NULL，
所以坑碰不到。Demo 里另建两张小表来演示：
t_ref 里插一个 NULL 之前，not in 给一行；插之后，变成 0 行。
数据一条没变，只是参照表里多了个 NULL。
原因是三值逻辑：x not in (1, 2, NULL) 等价于 x <> 1 and x <> 2 and x <> NULL，
而 x <> NULL 的结果是 NULL（未知），不是 TRUE。
只要有一个 NULL，整个 and 链条就永远不成立。
not exists 不受影响，
因为它判断的是「有没有匹配行」，NULL 比较失败就是「没匹配」，逻辑照样成立。
所以拿不准子查询有没有 NULL 时，用 not exists 比 not in 稳。
四、行转列与窗口
第 6 题把「一人三行」转成「一人一行、三门课各一列」，
标准写法是 max(case when 课程 = 1 then 分数 end)。
原理很简单：case 把不属于这一列的值变成 NULL，
max 再在组内把那个唯一的值捞出来。 用 min 也一样，因为每组里只有一个非 NULL 值。
用 sum 在这批数据上也一样对（sum 会忽略 NULL），
但一条记录如果匹配到多行，sum 会把它们加起来、max 只取一个——
那时候结果就不对了。所以透视一律用 max。
注意这题用的是 left join。 周杰的英语缺考，用 join 的话他整行都不会出现；
用 left join 他会出现在结果里、英语那格是空的——
这才是「缺考」的正确呈现。空着和 0 分，是两件事。
第 7 题是累计求和：
sum(...) over (partition by 学生 order by 课程) 是「组内累计」。
partition by 决定分组，order by 决定累计的顺序，默认的窗口范围是「从组头到当前行」。
这个写法在报表里到处都是：
累计销售额、累计注册人数、滚动三十天均值，都是同一个套路。
换成 avg 就是「到当前行为止的平均」，换成 count 就是「到当前行为止的计数」。
第 8 题是 lag：取「上一行」的值，lead 取下一行。
第一行没有上一行，所以是 NULL，
差值也就成了 NULL——这比填 0 更诚实。 要填默认值就写 lag(score, 1, 0)，
第二个参数是偏移几行，第三个是取不到时的默认值。
Demo 里张伟是「88 → 92 → 75」，差值是「空、+4、-17」；
王强是「72 → 65 → 80」，差值是「空、-7、+15」。 逐行对比用窗口函数一行就写完，
用自连接要写一堆条件，还容易漏边界。
五、执行顺序是一切的地基
第 9 题「个人平均分高于全校平均分的人」，用 having 加标量子查询。
having 后面可以用聚合函数，也可以和标量子查询比。
不能写成 where avg(sc.score) > …——
where 在 group by 之前执行，那时候还没有「组平均分」。
把执行顺序列出来，这一篇里所有「为什么不能这么写」就都有解释了：
| 顺序 | 阶段 | 能写什么 |
|---|---|---|
| 1 | from / join |
表、连接条件 |
| 2 | where |
行级条件，不能用聚合和窗口 |
| 3 | group by |
分组键 |
| 4 | having |
组级条件，能用聚合，不能用窗口 |
| 5 | 窗口函数 | 在分组之后算，所以不能提前筛 |
| 6 | select |
别名在这一步才出现 |
| 7 | order by / limit |
最后排序和截取 |
记住这张表，三件事就不用背了：窗口函数为什么不能进 where（where 在第 2 步，
窗口在第 5 步）；having 为什么能用聚合不能用窗口（第 4 步 vs 第 5 步）；
为什么 where 里不能引用 select 的别名（第 6 步才起名）。
六、九道题跑完，回头看数据
第 4 题删掉了周杰的英语成绩，
所以从第 4 题开始英语只有五条记录。 这不是 bug，是故意造的「缺考」。
这个变化会影响后面的平均分：第 9 题的全校平均是 82.12，
比 19.2 里那个 81.22 高了一点点——
因为把最低的 66 分（周杰英语）拿掉了。 同一批数据，少一行，平均分就变。
这也是「count(*) 不等于人数」的又一个例子：18 条变成 17 条，count(*) 立刻少一，
但学生还是六个人。 报「人均分」的时候用哪个分母，得说清楚。
七、动手 Demo：九道题，跑到哪算到哪
Demo 按「排名 → 缺考 → 透视 → 窗口 → 子查询」的顺序走， 九道题的输出全打印，包括那条错写法的结果。 第 4 题开头会删一条成绩造「缺考」， 所以从那里开始，英语的记录数变了。 对照着看就能发现「同一批数据、少一行、结论就变」。
# 九道高级题，重点在「为什么不能那样写」
# 1 每个学生最高分那门课：派生表 join
c.execute("SELECT s.name, c.name, sc.score FROM score sc "
"JOIN stu s ON s.id = sc.stu_id "
"JOIN course c ON c.id = sc.course_id "
"JOIN (SELECT stu_id, MAX(score) AS mx FROM score "
"GROUP BY stu_id) m "
"ON m.stu_id = sc.stu_id AND m.mx = sc.score")
# 2 每门课第一名：窗口函数先落成视图，外面才能筛
c.execute("CREATE VIEW v_rank AS SELECT c.name AS 课程, s.name AS 姓名, "
"sc.score AS 分数, sc.course_id AS cid, "
"RANK() OVER (PARTITION BY sc.course_id "
"ORDER BY sc.score DESC) AS 名次 "
"FROM score sc JOIN stu s ON s.id = sc.stu_id "
"JOIN course c ON c.id = sc.course_id")
c.execute("SELECT 课程, 姓名, 分数 FROM v_rank WHERE 名次 = 1")
# 3 每门课前两名
c.execute("SELECT 课程, 姓名, 分数 FROM v_rank WHERE 名次 <= 2")
# 4 英语缺考的人：反连接，条件必须写在 on 里
c.execute("SELECT s.name FROM stu s LEFT JOIN score sc "
"ON sc.stu_id = s.id AND sc.course_id = 3 "
"WHERE sc.stu_id IS NULL")
# 5 not in 遇 NULL 会整体失效（用两张小表演示）
c.execute("SELECT note FROM t_all "
"WHERE code NOT IN (SELECT r FROM t_ref)") # 正常：1 行
c.execute("INSERT INTO t_ref VALUES (NULL)")
c.execute("SELECT note FROM t_all "
"WHERE code NOT IN (SELECT r FROM t_ref)") # 0 行
c.execute("SELECT note FROM t_all WHERE NOT EXISTS "
"(SELECT 1 FROM t_ref WHERE t_ref.r = t_all.code)") # 1 行
# 6 行转列：max(case when ... end)
c.execute("SELECT s.name, "
"MAX(CASE WHEN sc.course_id = 1 THEN sc.score END) AS 语文, "
"MAX(CASE WHEN sc.course_id = 2 THEN sc.score END) AS 数学, "
"MAX(CASE WHEN sc.course_id = 3 THEN sc.score END) AS 英语 "
"FROM stu s LEFT JOIN score sc ON sc.stu_id = s.id "
"GROUP BY s.id ORDER BY s.id")
# 7 累计求和
c.execute("SELECT s.name, c.name, sc.score, "
"SUM(sc.score) OVER (PARTITION BY sc.stu_id "
"ORDER BY sc.course_id) AS 累计 "
"FROM score sc JOIN stu s ON s.id = sc.stu_id "
"JOIN course c ON c.id = sc.course_id")
# 8 和上一门课比：lag 取上一行
c.execute("SELECT s.name, c.name, sc.score, "
"LAG(sc.score) OVER (PARTITION BY sc.stu_id "
"ORDER BY sc.course_id) AS 上一门 "
"FROM score sc JOIN stu s ON s.id = sc.stu_id "
"JOIN course c ON c.id = sc.course_id")
# 9 个人平均高于全校平均：having + 标量子查询
c.execute("SELECT s.name, ROUND(AVG(sc.score), 2) FROM stu s "
"JOIN score sc ON sc.stu_id = s.id GROUP BY s.id "
"HAVING AVG(sc.score) > (SELECT AVG(score) FROM score)")
实跑输出：
--------------------------------------------------------------------
第八十六课：高级问题——九道题，题题有坑
--------------------------------------------------------------------
高级题到底难在哪
**难的不是语法，是「同一件事有好几种写法，选错了结果就不对」。**
**所以这一篇的重点不是「写出答案」，是「为什么不能那样写」。**
**有几题专门给了一条错写法 + 它的结果**，**看着像对的，跑出来是错的——这种最值钱。**
**还有一个共同点**：
**答案不止一行的时候，最容易出错**——
**「每门课的第一名」如果并列，你要一行还是两行？** **这个决定要写进 SQL 里，不能靠运气。**
第一题：每个学生最高分那门课
**第一种写法：派生表 `join`。** **先算出「每个学生的最高分」，再拿它去匹配原始行。**
SQL> SELECT s.name AS 姓名, c.name AS 课程, sc.score AS 分数 FROM score sc JOIN stu s
ON s.id = sc.stu_id JOIN course c ON c.id = sc.course_id JOIN (SELECT stu_id,
MAX(score) AS mx FROM score GROUP BY stu_id) m
ON m.stu_id = sc.stu_id AND m.mx = sc.score ORDER BY sc.score DESC
姓名 课程 分数
------------------------
孙悦 数学 96.00
李娜 语文 95.00
张伟 数学 92.00
赵敏 英语 88.00
王强 英语 80.00
周杰 英语 66.00
（6 行）
**第二种写法：行值 `in`。** **`(stu_id, score) in (子查询)` 是把两个列一起比**，
**SQLite 支持，MySQL 也支持。**
**跑出来 6 行**（孙悦·数学 96、李娜·语文 95、张伟·数学 92…），**和上面那张表一字不差。**
**两种写法的区别在可读性和移植性**：**行值 `in` 更短**，
**派生表 `join` 更通用**（有些老版本 MySQL 对行值 `in` 支持不好）。
**如果同一个人有两门课同分且都是最高，两种写法都会给出两行。**
**想要「只留一门」，得再加 `order by` 和 `limit`，或者用下一题的窗口函数。**
第二题：每门课的第一名
**这道题的关键是「窗口函数不能写进 `where`」**——**所以先把算好名次的结果装进一个视图：**
| CREATE VIEW v_rank AS SELECT c.name AS 课程, s.name AS 姓名,
| sc.score AS 分数, sc.course_id AS cid,
| RANK() OVER (PARTITION BY sc.course_id ORDER BY sc.score DESC)
| AS 名次
| FROM score sc JOIN stu s ON s.id = sc.stu_id
| JOIN course c ON c.id = sc.course_id
**视图装好之后，外面就剩一句筛选：**
SQL> SELECT 课程, 姓名, 分数, 名次 FROM v_rank WHERE 名次 = 1
课程 姓名 分数 名次
---------------------------------
语文 李娜 95.00 1
数学 孙悦 96.00 1
英语 孙悦 93.00 1
（3 行）
**`rank() over (partition by 课程 order by 分数 desc)` 的意思是**：
**按课程分组，组内按分数降序排，给每行一个名次。** **并列第一的两行，`rank()` 都给 1**，
**下一名是 3**（因为占了两个位置）。
**换成 `row_number()` 就只给一个 1、一个 2**，
**并列的人被硬排出先后，而且这个先后是随机的。** **`dense_rank()` 则是 1、1、2**——
**名次不跳号。** **三个函数的差别就在「并列时怎么算」，做排行榜时选错会被人投诉。**
**这批数据里没有并列，所以看不出差别。**
**把张伟的数学从 92 改成 96，和孙悦并列第一，再比一次：**
SQL> SELECT 课程, 姓名, 分数, 跳号, 不跳号, 硬排 FROM v_r3 WHERE 课程 = '数学'
课程 姓名 分数 跳号 不跳号 硬排
-------------------------------------------------------
数学 张伟 96.00 1 1 1
数学 孙悦 96.00 1 1 2
数学 李娜 88.00 3 2 3
数学 赵敏 79.00 4 3 4
数学 王强 65.00 5 4 5
数学 周杰 58.00 6 5 6
（6 行）
**两个 96 分并列第一**：**`rank` 给 `1、1、3`（跳号）**，
**`dense_rank` 给 `1、1、2`（不跳号）**，
**`row_number` 硬排出 `1、2、3、4…`**——**并列那两人谁前谁后，看运气。**
**`window w as (…)` 这个写法是「给窗口起个名字」**，
**三个函数共用一个定义，省得抄三遍。**
**SQLite 和 MySQL 8.0 都支持，老版本 MySQL 没有窗口函数，只能靠自连接硬算。**
**改回去了。** **后面几题还要用原始数据。**
第三题：每门课的前两名
**上一题那个 `v_rank` 视图直接就能用**，只把条件从 `= 1` 改成 `<= 2`：
SQL> SELECT 课程, 姓名, 分数, 名次 FROM v_rank WHERE 名次 <= 2 ORDER BY 课程, 名次
课程 姓名 分数 名次
---------------------------------
数学 孙悦 96.00 1
数学 张伟 92.00 2
英语 孙悦 93.00 1
英语 李娜 91.00 2
语文 李娜 95.00 1
语文 孙悦 91.00 2
（6 行）
**窗口函数不能直接写在 `where` 里**，
**因为 `where` 比窗口函数先执行。** **所以必须套一层（视图或者子查询），在外面筛。**
**这是窗口函数最常见的一个语法坑**——**报错信息通常很难懂。**
**`where` → `group by` → `having` → 窗口函数 → `order by` → `limit`**，
**这个执行顺序决定了「什么能写在哪儿」。** **`having` 里能用聚合但不能用窗口，就是这个原因。**
第四题：缺考的人
**先删掉周杰的英语成绩，造一个「缺考」。** **现在英语只有五条成绩，学生有六个。**
**正确写法是反连接（`left join` + `is null`）：**
SQL> SELECT s.name AS 姓名 FROM stu s LEFT JOIN score sc
ON sc.stu_id = s.id AND sc.course_id = 3 WHERE sc.stu_id IS NULL
姓名
-----
周杰
（1 行）
**`left join` 保留左表所有行，匹配不上的右表列全是 `NULL`。**
**所以「匹配不上」就等于「右表主键是 NULL」。**
**注意 `and sc.course_id = 3` 必须写在 `on` 里，不能写在 `where` 里。**
**写进 `where` 会把左连接退化成内连接**——
**`where` 会先把 `NULL` 那行滤掉，剩下全是能匹配上的：**
SQL> SELECT s.name AS 姓名 FROM stu s LEFT JOIN score sc ON sc.stu_id = s.id
WHERE sc.course_id = 3
姓名
-----
张伟
李娜
王强
赵敏
孙悦
（5 行）
**这就是写错的样子：出来五个人，全是「有英语成绩」的。**
**需求要的是「没有成绩」的那一个，结果正好反过来。** **这个错很常见，而且不报错**——
**它只是安静地给出一批错的人。**
第五题：not in 的 NULL 陷阱
**`not in` 的写法看着更直白，这一题的结果也一样：**
SQL> SELECT name AS 姓名 FROM stu
WHERE id NOT IN (SELECT stu_id FROM score WHERE course_id = 3)
姓名
-----
周杰
（1 行）
**但它有个致命前提：子查询那一列不能有 `NULL`。**
**这个案例库因为主键约束，`stu_id` 压根插不进 `NULL`**——
**所以这个坑在这套库里碰不到，得换两张小表演示：**
SQL> SELECT code, note FROM t_all
code note
--------------
1 甲
2 乙
3 丙
（3 行）
SQL> SELECT r FROM t_ref
r
--
1
2
（2 行）
**查「`code` 不在 `t_ref` 里的行」，`not in` 正常：**
SQL> SELECT note FROM t_all WHERE code NOT IN (SELECT r FROM t_ref)
note
-----
丙
（1 行）
**现在往 `t_ref` 插一个 `NULL`：**
SQL> SELECT r FROM t_ref
r
-----
1
2
NULL
（3 行）
SQL> SELECT note FROM t_all WHERE code NOT IN (SELECT r FROM t_ref)
note
-----
（0 行）
**结果从一行变成 0 行。** **数据一条没变，只是参照表里多了个 `NULL`。**
**换成 `not exists` 就没事：**
SQL> SELECT note FROM t_all WHERE NOT EXISTS (SELECT 1 FROM t_ref WHERE t_ref.r = t_all.code)
note
-----
丙
（1 行）
**原因是三值逻辑**：**`x not in (1, 2, NULL)` 等价于 `x <> 1 and x <> 2 and x <> NULL`**，
**而 `x <> NULL` 的结果是 `NULL`（未知），不是 `TRUE`。**
**只要有一个 `NULL`，整个 `and` 链条就永远不成立。**
**`not exists` 不受影响**，
**因为它判断的是「有没有匹配行」，`NULL` 比较失败就是「没匹配」，逻辑照样成立。**
**所以拿不准子查询有没有 `NULL` 时，用 `not exists` 比 `not in` 稳。**
第六题：行转列（成绩透视表）
SQL> SELECT s.name AS 姓名, MAX(CASE WHEN sc.course_id = 1 THEN sc.score END) AS 语文,
MAX(CASE WHEN sc.course_id = 2 THEN sc.score END) AS 数学,
MAX(CASE WHEN sc.course_id = 3 THEN sc.score END) AS 英语 FROM stu s LEFT JOIN score sc
ON sc.stu_id = s.id GROUP BY s.id ORDER BY s.id
姓名 语文 数学 英语
-----------------------------------
张伟 88.00 92.00 75.00
李娜 95.00 88.00 91.00
王强 72.00 65.00 80.00
赵敏 85.00 79.00 88.00
孙悦 91.00 96.00 93.00
周杰 60.00 58.00 NULL
（6 行）
**`max(case when … then 值 end)` 是行转列的标准写法。** **`case` 把不属于这一列的值变成 `NULL`**
，**`max` 再在组内把那个唯一的值捞出来。**
**用 `max` 还是 `min` 都行**，**因为每组里只有一个非 `NULL` 值。**
**用 `sum` 在这批数据上也一样对**（`sum` 会忽略 `NULL`），
**但一条记录如果匹配到多行，`sum` 会把它们加起来、`max` 只取一个**——
**那时候结果就不对了。** **所以透视一律用 `max`，别图省事。**
**注意这里用的是 `left join`。** **周杰的英语缺考，用 `join` 的话他整行都不会出现**，
**而用 `left join` 他会出现在结果里、英语那格是空的**——
**这才是「缺考」的正确呈现。** **空着和 0 分，是两件事。**
第七题：累计求和
SQL> SELECT s.name AS 姓名, c.name AS 课程, sc.score AS 分数,
SUM(sc.score) OVER (PARTITION BY sc.stu_id ORDER BY sc.course_id) AS 累计 FROM score sc
JOIN stu s ON s.id = sc.stu_id JOIN course c ON c.id = sc.course_id
WHERE sc.stu_id IN (1, 2) ORDER BY sc.stu_id, sc.course_id
姓名 课程 分数 累计
-----------------------------------
张伟 语文 88.00 88.00
张伟 数学 92.00 180.00
张伟 英语 75.00 255.00
李娜 语文 95.00 95.00
李娜 数学 88.00 183.00
李娜 英语 91.00 274.00
（6 行）
**`sum(...) over (partition by 学生 order by 课程)` 是「组内累计」。**
**`partition by` 决定分组，`order by` 决定累计的顺序**，**默认的窗口范围是「从组头到当前行」。**
**这个写法在报表里到处都是**：
**累计销售额、累计注册人数、滚动三十天均值，都是同一个套路。**
**换成 `avg` 就是「到当前行为止的平均」**，**换成 `count` 就是「到当前行为止的计数」。**
第八题：和上一门课比
SQL> SELECT s.name AS 姓名, c.name AS 课程, sc.score AS 分数,
LAG(sc.score) OVER (PARTITION BY sc.stu_id ORDER BY sc.course_id) AS 上一门,
sc.score - LAG(sc.score) OVER (PARTITION BY sc.stu_id ORDER BY sc.course_id) AS 差值
FROM score sc JOIN stu s ON s.id = sc.stu_id JOIN course c ON c.id = sc.course_id
WHERE sc.stu_id IN (1, 3) ORDER BY sc.stu_id, sc.course_id
姓名 课程 分数 上一门 差值
----------------------------------------------
张伟 语文 88.00 NULL NULL
张伟 数学 92.00 88.00 4.00
张伟 英语 75.00 92.00 -17.00
王强 语文 72.00 NULL NULL
王强 数学 65.00 72.00 -7.00
王强 英语 80.00 65.00 15.00
（6 行）
**`lag(列)` 取「上一行」的值，`lead(列)` 取「下一行」。** **第一行没有上一行，所以是 `NULL`**，
**差值也就成了 `NULL`——这比填 0 更诚实。** **要填默认值就写 `lag(score, 1, 0)`**，
**第二个参数是偏移几行，第三个是取不到时的默认值。**
**张伟的数学比语文高 4，英语比数学低 17。** **王强的数学比语文低 7，英语又比数学高 15。**
**这种「逐行对比」用窗口函数一行就写完**，**用自连接要写一堆条件，还容易漏边界。**
第九题：平均分高于全校平均的学生
SQL> SELECT s.name AS 姓名, ROUND(AVG(sc.score), 2) AS 平均分 FROM stu s JOIN score sc
ON sc.stu_id = s.id GROUP BY s.id HAVING AVG(sc.score) > (SELECT AVG(score) FROM score)
ORDER BY 平均分 DESC
姓名 平均分
----------------
孙悦 93.33
李娜 91.33
张伟 85.00
赵敏 84.00
（4 行）
**`having` 后面可以用聚合函数，也可以用标量子查询。**
**这里的子查询返回一个数（全校平均分），所以能直接比。**
**不能写成 `where avg(sc.score) > …`**——
**`where` 在 `group by` 之前执行，那时候还没有「组平均分」。**
**这也是「执行顺序决定什么能写在哪」的又一个例子。**
**顺便把全校平均分也打出来，好核对：**
SQL> SELECT ROUND(AVG(score), 2) AS 全校平均分 FROM score
全校平均分
-----------
82.12
（1 行）
**82.12 是全校平均**，**上面那四个人的个人平均都在它之上。**
**周杰平均 59.00，被筛掉了**——**他语文 60、数学 58、英语缺考。**
九道题的共同点
**第一，答案「不止一行」时最危险。** **每门课的第一名如果并列，你要一行还是两行？**
**这个决定必须写进 SQL，不能靠数据凑巧。**
**第二，`NULL` 到处都是坑。**
**缺考是 `NULL`、`not in` 里怕 `NULL`、`lag` 第一行是 `NULL`、`left join` 匹配不上是 `NULL`。**
**凡是「可能没有值」的地方，都要停一下想想 `NULL` 会怎么走。**
**第三，执行顺序是所有语法的地基。**
**`where` 比窗口函数先、`having` 比 `select` 后、窗口函数不能进 `where`。**
**记住顺序，这些「为什么不能这么写」就不用背了。**
第 1 题两种写法都是六行：孙悦·数学 96、李娜·语文 95、张伟·数学 92、赵敏·英语 88、
王强·英语 80、周杰·英语 66。 一人一行，因为没有同分并列。
第 2 题三门课的第一名：语文李娜 95、数学孙悦 96、英语孙悦 93。 孙悦一个人占了两门。
造了并列之后 rank 和 dense_rank 分开了：张伟、孙悦都是 96，rank 给 1、1、3，
dense_rank 给 1、1、2，row_number 给 1、2。 这就是「并列怎么算」的三种答案。
第 4 题两条语句对比最值钱：on 里那版给「周杰」一个人，where 里那版给「张伟、李娜、王强、
赵敏、孙悦」五个人。 后者全是「有成绩」的，和需求正好相反。
第 5 题 not in 从 1 行变 0 行，只是往 t_ref 插了一个 NULL；
not exists 那版仍然是 1 行。 三行输出把整件事说完了。
第 6 题透视表里周杰的英语是 NULL，不是 0——因为他缺考。
这也是为什么这题必须用 left join。
第 7、8 题的累计和差值：张伟 88 → 180 → 255，差值 空、+4、-17；
王强 72 → 137 → 217，差值 空、-7、+15。
第 9 题筛出四个人：孙悦 93.33、李娜 91.33、张伟 85.00、赵敏 84.00，全校平均 82.12。
周杰平均 59.00，被筛掉了。
30 秒实验
- 把第 3 题的
名次 <= 2改成名次 <= 1，和直接用= 1有什么区别？ - 第 4 题把
and sc.course_id = 3从on挪到where，结果变成哪五个人？ 为什么正好是「有成绩的」？ - 第 6 题把
max换成sum，在这批数据上结果会变吗？什么情况下才会变？
常见误区
- 把窗口函数写进
where。where比窗口函数先执行，必须套一层视图或子查询在外面筛。 left join的附加条件写进where。 那会把左连接退化成内连接， 「找没匹配上的行」就永远查不出来。- 用
not in查反连接。 子查询里一旦有NULL，结果整体变空，拿不准就换not exists。
自测三问（点开对答案）
1. `rank`、`dense_rank`、`row_number` 在并列时分别怎么给名次？ —— `rank` 跳号（1、1、3），`dense_rank` 不跳号（1、1、2），`row_number` 硬排（1、2， 谁前谁后不确定）。 2. 为什么 `left join` 的附加条件必须写在 `on` 里？ —— 写在 `on` 里表示「连接时按这个条件找」，匹配不上的左表行仍然保留； 写在 `where` 里是先连接再过滤，会把右表为 `NULL` 的行滤掉，左连接退化成内连接。 3. 窗口函数为什么不能写在 `where` 里？ —— 因为 `where` 在分组之前执行，窗口函数在分组之后才算，执行时窗口结果还不存在。 要筛必须先落成视图或子查询。第 19 章完了， 全书也完了。 四张表、十九道题、一套从建库到窗口函数的完整链路。 回头翻翻第 1 章那个「文件存数据不够用」，现在应该能一条一条说出为什么了。
- ☐ （全书完）