LeetCode Hot 100 · SQL 篇

一句话结论（30s）

SQL 题的底层套路只有一句：“先分组，再在组内做变换”——GROUP BY 聚合、窗口函数排名、NOT EXISTS 反向过滤、CASE WHEN 行转列、GROUP_CONCAT 集合比较，本质上都是把多行压成一行或把行映射成列，因为绝大多数 SQL 面试题都是在考”行 ↔ 列 ↔ 组”这三者的互相转换。

核心原理（2min）

五大技巧各记一句口诀：

底层深入（5-10min）

分组聚合三连问

Q1: 每个班的平均分

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

Q2: 每个班分数最高的人

SELECT s.student_id, s.class_id, s.score
FROM student s
JOIN (SELECT class_id, MAX(score) AS max_score FROM student GROUP BY class_id) t
  ON s.class_id = t.class_id AND s.score = t.max_score;

同分并列会返回多行——确认业务是否允许。

Q3: 每个班级前五名

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS rk
  FROM student
) t WHERE rk <= 5;

ROW_NUMBER vs RANK vs DENSE_RANK——看业务需求选。

没学过”张三”老师课的学生

SELECT s_id, s_name FROM Student
WHERE NOT EXISTS (
    SELECT 1 FROM Score sc
    JOIN Course c ON sc.c_id = c.c_id
    JOIN Teacher t ON c.t_id = t.t_id
    WHERE t.t_name = '张三' AND sc.s_id = Student.s_id
);

NOT EXISTS 比 NOT IN 更安全——子查询中有 NULL 时 NOT IN 返回空集。

💭 思考：为什么「没学过张三老师课」要用 NOT EXISTS 而不是 NOT IN？——从「NULL 会把比较变成什么」反推：SQL 是三值逻辑，NULL 参与比较的结果不是 true/false 而是 unknown。x NOT IN (a, NULL) 会展开成 x <> a AND x <> NULL，而 x <> NULL 永远是 unknown，整条 AND 就永远不是 true，结果返回空集——这是最隐蔽的坑。而 NOT EXISTS (SELECT 1 ... WHERE 条件) 判断的是「子查询是否存在匹配行」，NULL 只是「不匹配」，不影响「存在性」的判断。所以反向过滤优先 NOT EXISTS，不是语法偏好，而是它在三值逻辑下语义更稳。如果不这样，数据里一旦混进一个 NULL，你的「没学过」就悄悄变成了「没有人」。

CASE WHEN 行转列

SELECT name,
  MAX(CASE WHEN subject = '语文' THEN score END) AS 语文,
  MAX(CASE WHEN subject = '数学' THEN score END) AS 数学,
  MAX(CASE WHEN subject = '英语' THEN score END) AS 英语
FROM scores
GROUP BY name;

MAX 的作用：GROUP BY 把三行合并为一行，MAX 从”一个非 NULL 值 + 两个 NULL”中挑出唯一有效值。

💭 思考：为什么行转列要「CASE WHEN 造列 + MAX 挑值」两件套，MAX 在这里起什么作用？——从「GROUP BY 把三行压成一行后，列里剩什么」反推：每个学生三门课是三行，按 name 分组后，语文列里是「一个真实分数 + 两个 NULL」（其他两行 CASE WHEN 不命中语文，返回 NULL）。这时要的「语文分数」就是「唯一那个非 NULL 值」，而聚合函数 MAX 会忽略 NULL——所以 MAX 在这里不是「取最大」，而是「挑出唯一的非 NULL 值」，MIN / SUM 也行（只有一个非 NULL 时结果相同），MAX 只是约定俗成。如果不加聚合、直接 GROUP BY 后 SELECT CASE WHEN...，会报「非聚合列不在 GROUP BY 里」的错——CASE 列必须先被聚合函数包起来，这是行转列最容易漏的一步。

GROUP_CONCAT 集合比较

-- 找出和"张三"选修完全一样课程的学生
SELECT student_id FROM enrollments
GROUP BY student_id
HAVING GROUP_CONCAT(course_id ORDER BY course_id) =
       (SELECT GROUP_CONCAT(course_id ORDER BY course_id)
        FROM enrollments WHERE student_id = 1);

集合相等性 → 有序字符串相等性。

💭 思考：为什么「选修课程完全相同」能转成「GROUP_CONCAT 拼串相等」？——从「集合比较难在哪」反推：SQL 里没有直接的集合相等运算符，逐个元素比对又麻烦。而集合有两个天然属性：无序、不可重复。解法就是把集合「规范化」成一个可比的对象——GROUP_CONCAT(course_id ORDER BY course_id) 用 ORDER BY 消掉无序性、用拼串把多值压成一个字符串，于是「集合相等」退化成「字符串相等」。这跟行转列、布尔聚合是同一个底层套路：把「多行 / 集合」压成「一行 / 标量」，再用熟悉的比较去处理。如果不这样，你就得用「A 全在 B 且 B 全在 A」的双向包含去硬写，又绕又容易错。

HAVING 布尔表达式技巧

-- 查询所有科目都及格的学生
SELECT student_id FROM scores
GROUP BY student_id
HAVING SUM(score < 60) = 0;  -- score<60 返回 1(true) 或 0(false)

利用 MySQL 的隐式 0/1 转换——SUM(score < 60) 统计不合格科目数。

章末提问

追问 1：SQL 题的底层套路是什么？五大技巧的共同点是什么？

结论先行：都是「先分组，再在组内做变换」，考的是「行 ↔ 列 ↔ 组」三者的互相转换。 因为：GROUP BY 聚合、窗口函数排名、NOT EXISTS 反向过滤、CASE WHEN 行转列、GROUP_CONCAT 集合比较，本质上都是把多行压成一行或把行映射成列。抓住这条主线，换表结构、加条件的变体都能归到同一套路里。

追问 2：NOT EXISTS 为什么比 NOT IN 安全？

结论先行：因为三值逻辑下，子查询里有 NULL 时 NOT IN 会返回空集。 因为：x NOT IN (a, NULL) 展开成 x <> a AND x <> NULL，而 x <> NULL 恒为 unknown，整条 AND 永远不是 true。NOT EXISTS 判断的是「是否存在匹配行」，NULL 只是「不匹配」，不影响存在性判断。所以反向过滤优先 NOT EXISTS，是语义更稳，不是语法偏好。

追问 3：行转列里 MAX 的作用是什么？不加聚合会怎样？

结论先行：MAX 不是取最大，而是从「一个非 NULL + 两个 NULL」里挑出唯一有效值。 因为：按 name 分组后，语文列里是「一个真实分数 + 两个 NULL」，MAX 会忽略 NULL，正好挑出唯一分数。不加聚合直接 SELECT CASE 列，会报「非聚合列不在 GROUP BY 里」——CASE 列必须先被聚合函数包起来。

追问 4：怎么比较「选修课程完全相同」这种集合相等？为什么加 ORDER BY？

结论先行：用 GROUP_CONCAT 把集合转成有序字符串，让「集合相等」退化成「字符串相等」。 因为：SQL 没有直接的集合相等运算符，而集合天然无序、不可重复。GROUP_CONCAT(course_id ORDER BY course_id) 用 ORDER BY 消掉无序性、用拼串把多值压成一个标量，于是集合比较变成字符串比较。这和行转列、布尔聚合是同一个「多行压成标量」的底层套路。