Go back

SQL 手撕题库——50 道高频题（含预备题与规律速查）

SQL 手撕题库——50 道高频题

每道题统一按 4 步讲：① 题目本质 ② 解题关键 ③ 核心 SQL ④ 可能的坑。 题号沿用原题库编号（部分题号在原题库中已空缺），标 ⭐ 的为重点题。

预备题：班级成绩三连问

A. 查询每个班的平均分和班级 id

① 题目本质：分组聚合问题

② 解题关键：按 class_id 分组，用 AVG(score)

③ 核心 SQL

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

class_id score student
select class_id, AVG(score) as avg_score
from student
group by class_id;

④ 可能的坑

B. 查询每个班分数最高的人的 id 和分数

① 题目本质：分组取最大值

② 解题关键：GROUP BY class_id + MAX(score)，再关联原表取 id

③ 核心 SQL

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

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

④ 可能的坑

C. 查询每个班级前五名

① 题目本质：分组内 TopN 排名

② 解题关键：窗口函数 ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC)

③ 核心 SQL

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

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

④ 可能的坑

第 1 题：01 课程比 02 课程成绩高的学生（重点）⭐

① 题目本质：同一学生两门课成绩的一对一比较

② 解题关键：把 01 和 02 成绩分别派生为两张表，再 INNER JOIN 关联同一个 s_id 比较

③ 核心 SQL

SELECT s.s_id, s.s_name, s1.s_score01 `01`, s2.s_score02 `02`
FROM (SELECT s_id, s_score s_score01 FROM Score WHERE c_id = '01') s1
         INNER JOIN (SELECT s_id, s_score s_score02 FROM Score WHERE c_id = '02') s2
                    ON s1.s_id = s2.s_id
         INNER JOIN Student s ON s.s_id = s1.s_id
WHERE s1.s_score01 > s2.s_score02;

也可用 CASE WHEN 行转列 + HAVING 比较（更简洁）：

SELECT s_id, s_name,
       MAX(CASE WHEN c_id = '01' THEN s_score END) `01`,
       MAX(CASE WHEN c_id = '02' THEN s_score END) `02`
FROM Score
         INNER JOIN Student USING (s_id)
GROUP BY s_id, s_name
HAVING `01` > `02`;

④ 可能的坑

第 2 题：平均成绩大于 60 分的学生（重点）⭐

① 题目本质：聚合过滤

② 解题关键：GROUP BY s_id + AVG 聚合 + HAVING 过滤

③ 核心 SQL

SELECT s.s_id, s.s_name, FORMAT(AVG(sc.s_score), 2) avg_score
FROM Student s
         INNER JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
HAVING AVG(sc.s_score) > 60;

④ 可能的坑

第 3 题：所有学生的学号、姓名、选课数、总成绩（不重要）

① 题目本质：含 NULL 的聚合展示

② 解题关键：LEFT JOIN 保留没选课的学生；COUNT(c_id) 忽略 NULL；COALESCE 处理总分 NULL

③ 核心 SQL

SELECT s.s_id, s.s_name,
       COUNT(sc.c_id)              course_count,
       COALESCE(SUM(sc.s_score), 0) total_score
FROM Student s
         LEFT JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name;

④ 可能的坑

第 4 题：姓”猴”的老师个数（不重要）

① 题目本质：模糊匹配 + 计数

② 解题关键：LIKE '猴%' + COUNT(*)

③ 核心 SQL

SELECT COUNT(*) number
FROM Teacher
WHERE t_name LIKE '猴%';

④ 可能的坑

第 5 题：没学过”张三”老师课的学生（重点）⭐

① 题目本质：“没有”→ 反向过滤，典型 NOT EXISTS 场景

② 解题关键：先找”学过张三课的学生集合”，再取补集

③ 核心 SQL

-- NOT EXISTS 写法（推荐）
SELECT s.s_id, s.s_name
FROM Student s
WHERE NOT EXISTS (
    SELECT 1
    FROM Score sc
             INNER JOIN Course c ON sc.c_id = c.c_id
             INNER JOIN Teacher t ON c.t_id = t.t_id
    WHERE sc.s_id = s.s_id
      AND t.t_name = '张三'
);

SELECT s.s_id, s.s_name
FROM student s
WHERE NOT EXISTS(
SELECT 1
FROM score sc
INNER JOIN Course c on sc.c_id = c.c_id
INNer JOIN Teacher t on c.t_id = t.t_id
where sc.s_id = s.s_id AND t.t_name = '张三');

④ 可能的坑

想一想：为什么 NOT IN 一旦子查询结果里有 NULL，就整表变空？

因为 x NOT IN (1, NULL) 会被展开成 x != 1 AND x != NULL。任何值与 NULL 比较的结果都是 NULL（未知），未知 AND 真 仍是未知，WHERE 只保留结果为真的行，于是没有任何行能通过。NOT EXISTS 判断的是「是否存在这样一行」，不存在就是明确的不存在，不会被 NULL 干扰，所以更稳。

第 7 题：同时学过 01 和 02 课程的学生（重点）⭐

① 题目本质：集合交——一个学生必须同时满足两个条件

② 解题关键：对同一个 s_id 的课程做 GROUP BY + HAVING COUNT = 2

③ 核心 SQL

SELECT s.s_id, s.s_name
FROM Student s
         INNER JOIN (SELECT s_id
                     FROM Score
                     WHERE c_id IN ('01', '02')
                     GROUP BY s_id
                     HAVING COUNT(DISTINCT c_id) = 2) scc
                    ON s.s_id = scc.s_id;

④ 可能的坑

想一想：为什么这里必须用 COUNT(DISTINCT c_id) = 2，而不是 COUNT(*) = 2 或 COUNT(s_name) = 2？

因为同一学生可能对同一门课有多条成绩记录（补考、重修等），COUNT(*) 数的是行数，两行可能都是 01 课，就会误判成「学了两门」。COUNT(DISTINCT c_id) 数的是不同课程的个数，才真正表达了「同时学过 01 和 02」这个集合语义。

第 8 题：02 课程的总成绩（不重点）

① 题目本质：简单聚合

② 解题关键：WHERE c_id = '02' + SUM

③ 核心 SQL

SELECT SUM(s_score) total
FROM Score
WHERE c_id = '02';

④ 可能的坑

第 9A 题：有课程成绩小于 60 的学生（简单）

① 题目本质：行过滤，只要存在一门 < 60 即入选

② 解题关键：INNER JOIN + WHERE sc.s_score < 60 + DISTINCT 去重

③ 核心 SQL

SELECT DISTINCT s.s_id, s.s_name
FROM Student s
         INNER JOIN Score sc ON s.s_id = sc.s_id
WHERE sc.s_score < 60;

④ 可能的坑

第 9B 题：所有课程成绩都小于 60 的学生（难）

① 题目本质：全量满足条件——每门课都 < 60

② 解题关键：HAVING SUM(s_score >= 60) = 0（布尔转 0/1）或 NOT EXISTS 反向

③ 核心 SQL

-- HAVING 写法
SELECT s.s_id, s.s_name
FROM Student s
         INNER JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
HAVING SUM(sc.s_score >= 60) = 0;

-- NOT EXISTS 写法
SELECT s.s_id, s.s_name
FROM Student s
WHERE NOT EXISTS (
    SELECT 1
    FROM Score sc
    WHERE sc.s_id = s.s_id
      AND sc.s_score >= 60
);

④ 可能的坑

想一想：「有一门 < 60」和「所有课程都 < 60」差在哪？为什么后者更难？

「有一门 < 60」是存在性判断，一行 WHERE 就能表达；「所有课程都 < 60」是全称判断，等价于「不存在任何一门 ≥ 60 的课」。所以要把全称命题翻译成「没有反例」——用 NOT EXISTS 或 HAVING SUM(score >= 60) = 0 来表达。

第 10 题：没有学全所有课的学生（重点）⭐

① 题目本质：学生选课数 < 总课程数

② 解题关键：子查询统计该学生选课数，与 Course 总数比较

③ 核心 SQL

SELECT s.s_id, s.s_name
FROM Student s
WHERE s.s_id NOT IN (
    SELECT sc.s_id
    FROM Score sc
    GROUP BY sc.s_id
    HAVING COUNT(sc.c_id) = (SELECT COUNT(*) FROM Course)
);

④ 可能的坑

第 11 题：至少有一门课与 01 同学相同的学生（重点）⭐

① 题目本质：集合相交——课程集合有交集

② 解题关键：用 IN 子查询找出 01 同学选的课，再找选过其中任意一门的学生

③ 核心 SQL

SELECT DISTINCT s.s_id, s.s_name
FROM Student s
         INNER JOIN Score sc ON s.s_id = sc.s_id
WHERE sc.c_id IN (SELECT c_id FROM Score WHERE s_id = '01')
  AND s.s_id != '01';

④ 可能的坑

第 12 题：与 01 同学所学课程完全相同的其他同学（重点）⭐

① 题目本质：集合相等——双向包含

② 解题关键：

③ 核心 SQL

-- GROUP_CONCAT 字符串比较法（简洁）
SELECT scc.s_id
FROM (SELECT s_id, GROUP_CONCAT(c_id ORDER BY c_id SEPARATOR ',') course
      FROM Score
      GROUP BY s_id) scc
WHERE scc.course = (SELECT GROUP_CONCAT(c_id ORDER BY c_id SEPARATOR ',')
                    FROM Score
                    WHERE s_id = '01'
                    GROUP BY s_id)
  AND scc.s_id != '01';

-- COUNT 法（稳健）
SELECT scc.s_id
FROM Score scc
WHERE scc.c_id IN (SELECT c_id FROM Score WHERE s_id = '01')
  AND scc.s_id != '01'
GROUP BY scc.s_id
HAVING COUNT(DISTINCT scc.c_id) = (SELECT COUNT(DISTINCT c_id) FROM Score WHERE s_id = '01');

④ 可能的坑

想一想：为什么只检查「选的课都在 01 的课里」还不够，还必须 COUNT 相等？

因为「都在 01 的课里」只保证了子集关系（没选 01 之外的课），没保证数量：一个只选了 01 这一门课的学生也满足子集条件，却不是「完全相同」。子集 + 数量相等，才能推出集合相等。

第 15 题：两门及以上不及格的学生（重点）⭐

① 题目本质：分组聚合 + 条件筛选

② 解题关键：HAVING COUNT(s_score < 60 的行) >= 2，利用布尔表达式转 0/1

③ 核心 SQL

SELECT s.s_id, s.s_name, FORMAT(AVG(sc.s_score), 2) avg_score
FROM Student s
         INNER JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
HAVING SUM(sc.s_score < 60) >= 2;

④ 可能的坑

第 16 题：01 课程分数 < 60 按分数降序（不重点）

① 题目本质：简单过滤 + 排序

② 解题关键：WHERE c_id = '01' AND s_score < 60 + ORDER BY DESC

③ 核心 SQL

SELECT s.s_id, s.s_name, scc.s_score
FROM Student s
         INNER JOIN (SELECT s_id, s_score
                     FROM Score
                     WHERE s_score < 60
                       AND c_id = '01') scc
                    ON s.s_id = scc.s_id
ORDER BY scc.s_score DESC;

④ 可能的坑

第 17 题：按平均成绩展示所有课程成绩（重重点 = 35 题）⭐⭐

① 题目本质：行转列 + 排序

② 解题关键：CASE WHEN c_id = 'XX' THEN s_score END 行转列 + MAX 聚合去重 + 派生表提供均值

③ 核心 SQL

SELECT sc.s_id                                        `学号`,
       MAX(CASE WHEN c_id = '01' THEN s_score END)    `语文`,
       MAX(CASE WHEN c_id = '02' THEN s_score END)    `数学`,
       MAX(CASE WHEN c_id = '03' THEN s_score END)    `英语`,
       scc.avg                                        `平均成绩`
FROM Score sc
         INNER JOIN (SELECT s_id, FORMAT(AVG(s_score), 2) avg
                     FROM Score
                     GROUP BY s_id) scc ON sc.s_id = scc.s_id
GROUP BY sc.s_id
ORDER BY scc.avg DESC;

④ 可能的坑

第 18 题：各科最高最低平均分及各段率（综合重点）⭐

① 题目本质：多维聚合展示

② 解题关键：CASE WHEN 配合 SUM / COUNT 计算比率；标准写法比 AVG(布尔) 更通用

③ 核心 SQL

SELECT sc.c_id                                                                                                `课程ID`,
       c.c_name                                                                                               `课程名`,
       MAX(sc.s_score)                                                                                        `最高分`,
       MIN(sc.s_score)                                                                                        `最低分`,
       FORMAT(AVG(sc.s_score), 2)                                                                             `平均分`,
       FORMAT(SUM(CASE WHEN sc.s_score >= 60 THEN 1 ELSE 0 END) / COUNT(sc.s_id), 2)                         `及格率`,
       FORMAT(SUM(CASE WHEN sc.s_score >= 70 AND sc.s_score < 80 THEN 1 ELSE 0 END) / COUNT(sc.s_id), 2)     `中等率`,
       FORMAT(SUM(CASE WHEN sc.s_score >= 80 AND sc.s_score < 90 THEN 1 ELSE 0 END) / COUNT(sc.s_id), 2)     `优良率`,
       FORMAT(SUM(CASE WHEN sc.s_score >= 90 THEN 1 ELSE 0 END) / COUNT(sc.s_id), 2)                         `优秀率`
FROM Score sc
         INNER JOIN Course c ON sc.c_id = c.c_id
GROUP BY sc.c_id, c.c_name;

④ 可能的坑

第 19 题：各科成绩排名（重点窗口函数）⭐

① 题目本质：分组内排名

② 解题关键：ROW_NUMBER() OVER (PARTITION BY c_id ORDER BY s_score DESC)

③ 核心 SQL

SELECT s_id, c_id,
       ROW_NUMBER() OVER (PARTITION BY c_id ORDER BY s_score DESC) `rank`,
       s_score
FROM Score;

三种窗口函数区别：

  • ROW_NUMBER：1 2 3 4（无并列）
  • DENSE_RANK：1 2 2 3（并列不跳号）
  • RANK：1 2 2 4（并列跳号）

④ 可能的坑

想一想：ROW_NUMBER、RANK、DENSE_RANK 在并列时行为有何不同，什么时候该用后两者？

ROW_NUMBER 永远连续编号 1 2 3 4，并列会被强行拆开；RANK 并列同号、下一名跳号（1 2 2 4）；DENSE_RANK 并列同号且不跳号（1 2 2 3）。业务要「并列算同一名次」时用 RANK 或 DENSE_RANK，要「严格取固定人数」时用 ROW_NUMBER。

第 20 题：学生总成绩排名（不重点）

① 题目本质：全局排名（不分组）

② 解题关键：先 GROUP BY 算总分，再用 ROW_NUMBER() OVER (ORDER BY 总分 DESC)

③ 核心 SQL

SELECT ROW_NUMBER() OVER (ORDER BY SUM(s_score) DESC) `rank`,
       s_id,
       SUM(s_score) total
FROM Score
GROUP BY s_id;

④ 可能的坑

第 21 题：不同老师所教课程平均分排序（不重点）

① 题目本质：多表 JOIN + 分组 + 排序

② 解题关键：联表拿到老师名和课程名后 GROUP BY 两字段

③ 核心 SQL

SELECT t.t_name, c.c_name, FORMAT(AVG(sc.s_score), 2) avg_score
FROM Score sc
         INNER JOIN Course c ON sc.c_id = c.c_id
         INNER JOIN Teacher t ON c.t_id = t.t_id
GROUP BY t.t_name, c.c_name
ORDER BY avg_score DESC;

④ 可能的坑

第 22 题：各课程成绩第 2 ~ 3 名的学生（重要）⭐

① 题目本质：分组内取指定名次区间

② 解题关键：窗口函数分组排名后，外层 WHERE rank BETWEEN 2 AND 3

③ 核心 SQL

SELECT scc.c_id, s.s_id, s.s_name, scc.`rank`, scc.s_score
FROM Student s
         INNER JOIN (SELECT *,
                            ROW_NUMBER() OVER (PARTITION BY c_id ORDER BY s_score DESC) `rank`
                     FROM Score) scc
                    ON s.s_id = scc.s_id
WHERE scc.`rank` BETWEEN 2 AND 3;

④ 可能的坑

第 23 题：各科分段人数统计（重点）⭐

① 题目本质：分段统计，行转列

② 解题关键：CASE WHEN 区间判断 + SUM 聚合

③ 核心 SQL

SELECT c.c_id                                                                 `课程ID`,
       c.c_name                                                               `课程名称`,
       SUM(CASE WHEN sc.s_score >= 85 THEN 1 ELSE 0 END)                      `[100-85]`,
       SUM(CASE WHEN sc.s_score >= 70 AND sc.s_score < 85 THEN 1 ELSE 0 END)  `[85-70]`,
       SUM(CASE WHEN sc.s_score >= 60 AND sc.s_score < 70 THEN 1 ELSE 0 END)  `[70-60]`,
       SUM(CASE WHEN sc.s_score < 60 THEN 1 ELSE 0 END)                       `[<60]`
FROM Course c
         INNER JOIN Score sc ON c.c_id = sc.c_id
GROUP BY c.c_id, c.c_name;

④ 可能的坑

第 24 题：学生平均成绩及名次（重点）⭐

① 题目本质：全局排名

② 解题关键：GROUP BY 算均值后，ROW_NUMBER() OVER (ORDER BY AVG DESC)

③ 核心 SQL

SELECT s_id,
       FORMAT(AVG(s_score), 2)                        avg_score,
       ROW_NUMBER() OVER (ORDER BY AVG(s_score) DESC) `rank`
FROM Score
GROUP BY s_id;

④ 可能的坑

第 25 题：各科成绩前三名（重点，同 22 题）⭐

① 题目本质：分组内 Top3

② 解题关键：ROW_NUMBER() OVER (PARTITION BY c_id ORDER BY s_score DESC)，外层 WHERE rank <= 3

③ 核心 SQL

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY c_id ORDER BY s_score DESC) rk
      FROM Score) t
WHERE rk <= 3;

④ 可能的坑：同第 22 题

第 26 题：每门课程被选修的学生数（不重点）

① 题目本质：分组计数

② 解题关键：GROUP BY c_id + COUNT

③ 核心 SQL

SELECT c.c_name, COUNT(sc.c_id) number
FROM Score sc
         INNER JOIN Course c ON sc.c_id = c.c_id
GROUP BY c.c_name;

④ 可能的坑：JOIN 记得写 ON

第 27 题：只选了两门课的学生（不重点）

① 题目本质：分组计数 = 2

② 解题关键：GROUP BY + HAVING COUNT(DISTINCT c_id) = 2

③ 核心 SQL

SELECT s.s_id, s.s_name
FROM Student s
         INNER JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
HAVING COUNT(DISTINCT c_id) = 2;

④ 可能的坑：能用 INNER JOIN ON 就不用 IN，IN 有时不走索引

第 28 题：男女生人数（不重点）

① 题目本质：条件计数

② 解题关键：SUM(CASE WHEN sex = '男' THEN 1 ELSE 0 END)

③ 核心 SQL

SELECT SUM(CASE WHEN s_sex = '男' THEN 1 ELSE 0 END) `男`,
       SUM(CASE WHEN s_sex = '女' THEN 1 ELSE 0 END) `女`
FROM Student;

④ 可能的坑：简单场景也可用 IF(condition, 1, 0)，但面试写 CASE WHEN 更稳

第 29 题：姓名含”风”的学生（不重点）

① 题目本质：模糊匹配

② 解题关键：LIKE '%风%'

③ 核心 SQL

SELECT * FROM Student WHERE s_name LIKE '%风%';

④ 可能的坑：% 在两端会导致全表扫描，无法用索引

第 31 题：1990 年出生的学生（重点，日期函数）⭐

① 题目本质：日期字段过滤

② 解题关键：YEAR(s_birth) = 1990；如果字段是 VARCHAR 不能依赖 BETWEEN

③ 核心 SQL

SELECT * FROM Student WHERE YEAR(s_birth) = 1990;

常用时间函数（加分项）：

SELECT DATE_FORMAT(NOW(), '%W %M %Y'),
       DATE_ADD(NOW(), INTERVAL 7 DAY),
       DATEDIFF(NOW(), '2025-01-13');

④ 可能的坑

第 32 题：平均成绩 ≥ 85 的学生（不重要）

① 题目本质：聚合过滤

② 解题关键：HAVING AVG(s_score) >= 85

③ 核心 SQL

SELECT s.s_id, s.s_name, FORMAT(AVG(sc.s_score), 2) avg_score
FROM Student s
         INNER JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
HAVING AVG(sc.s_score) >= 85;

④ 可能的坑：聚合过滤不能用 WHERE

第 33 题：各课程平均分排序（不重要）

① 题目本质：排序优先级

② 解题关键：ORDER BY avg ASC, c_id DESC（多字段排序，靠前优先级高）

③ 核心 SQL

SELECT c_id, FORMAT(AVG(s_score), 2) avg
FROM Score
GROUP BY c_id
ORDER BY avg ASC, c_id DESC;

④ 可能的坑：ORDER BY 多字段排序以靠近 ORDER BY 的字段优先

第 34 题：数学不及格的学生（不重点）

① 题目本质：多表联接过滤

② 解题关键：联表拿到课程名后 WHERE c_name = '数学' AND s_score < 60

③ 核心 SQL

SELECT s.s_name, sc.s_score
FROM Student s
         INNER JOIN Score sc ON s.s_id = sc.s_id
         INNER JOIN Course c ON sc.c_id = c.c_id
WHERE c.c_name = '数学'
  AND sc.s_score < 60;

④ 可能的坑：USING(col) 是 ON t1.col = t2.col 的语法糖，字段名相同时可用

第 35 题：所有学生的课程及分数（行转列重点）⭐

① 题目本质：行转列（Pivot）

② 解题关键：MAX(CASE WHEN c_name = 'XX' THEN s_score END) + GROUP BY s_name；用 LEFT JOIN 保留无选课学生

③ 核心 SQL

SELECT s.s_name,
       MAX(CASE WHEN c_name = '语文' THEN s_score END) `语文`,
       MAX(CASE WHEN c_name = '数学' THEN s_score END) `数学`,
       MAX(CASE WHEN c_name = '英语' THEN s_score END) `英语`,
       MAX(CASE WHEN c_name = '化学' THEN s_score END) `化学`
FROM Student s
         LEFT JOIN Score sc USING (s_id)
         LEFT JOIN Course c USING (c_id)
GROUP BY s.s_name;

④ 可能的坑

想一想：为什么 CASE WHEN 行转列必须配 MAX（或其他聚合函数）？

GROUP BY 之后每个学生只输出一行，但 CASE WHEN c_id = '语文' 只在「语文课那一行」有值、其余行为 NULL。不加聚合会触发 only_full_group_by 报错，且每个学生被拆成多行。MAX 会在一堆 NULL 和唯一一个真实分数里挑出那个真实值，把「行」压成「列」。

第 36 题：任意一门课成绩 > 70 的学生（重点）⭐

① 题目本质：行过滤，存在即入选

② 解题关键：INNER JOIN + WHERE s_score > 70（只要有一行满足条件即可）

③ 核心 SQL

SELECT s.s_name, c.c_name, sc.s_score
FROM Student s
         INNER JOIN Score sc USING (s_id)
         INNER JOIN Course c USING (c_id)
WHERE sc.s_score > 70;

④ 可能的坑：同一学生多门课 > 70 会出现多行，看需求决定是否 DISTINCT

第 37 题：不及格课程按课程号降序（不重点）

① 题目本质：过滤 + 排序

② 解题关键：WHERE s_score < 60 ORDER BY c_id DESC

③ 核心 SQL

SELECT s_id, c_id
FROM Score
WHERE s_score < 60
ORDER BY s_id, c_id DESC;

④ 可能的坑：多字段排序时 s_id 默认 ASC，c_id 明确 DESC

第 38 题：03 课程成绩 > 80 的学生（不重要）

① 题目本质：联表 + 双条件过滤

② 解题关键：WHERE c_id = '03' AND s_score > 80

③ 核心 SQL

SELECT s.s_id, s.s_name
FROM Student s
         INNER JOIN Score sc USING (s_id)
WHERE sc.c_id = '03'
  AND sc.s_score > 80;

④ 可能的坑：>80 还是 >=80，仔细看题目数字

第 39 题：每门课程的学生人数（不重要）

① 题目本质：分组计数

② 解题关键：GROUP BY c_id + COUNT(DISTINCT s_id)

③ 核心 SQL

SELECT c_id, COUNT(DISTINCT s_id) student_count
FROM Score
GROUP BY c_id;

④ 可能的坑：COUNT(*) vs COUNT(s_id)，有 NULL 时结果不同

第 40 题：张三老师课程中成绩最高的学生（重要，Top1）⭐

① 题目本质：多表联接 + 取最大值 Top1

② 解题关键：联表过滤出张三的课程后 ORDER BY DESC LIMIT 1

③ 核心 SQL

SELECT s.s_name, sc.s_score
FROM Student s
         INNER JOIN Score sc USING (s_id)
         INNER JOIN Course c USING (c_id)
         INNER JOIN (SELECT * FROM Teacher WHERE t_name = '张三') t USING (t_id)
ORDER BY sc.s_score DESC
LIMIT 0, 1;

④ 可能的坑

第 41 题：不同课程成绩相同的学生（重点，自连接）⭐

① 题目本质：存在相同分值但来自不同课程

② 解题关键：子查询找出”同分且来自多门课”的分数，再 JOIN 回去

③ 核心 SQL

SELECT sc.s_id, sc.c_id, sc.s_score
FROM Score sc
         INNER JOIN (SELECT s_score
                     FROM Score
                     GROUP BY s_score
                     HAVING COUNT(*) > 1
                        AND COUNT(DISTINCT c_id) > 1) scc
                    ON sc.s_score = scc.s_score
ORDER BY sc.s_score DESC;

自连接补充（某学生某课成绩是否有人比他低）：

SELECT s1.*, s2.*
FROM Score s1
         JOIN Score s2 ON s1.c_id = s2.c_id AND s2.s_score > s1.s_score
ORDER BY s1.s_id;

④ 可能的坑：自连接使用同一张表两个别名，务必在 ON 条件中限制清楚关联逻辑

第 43 题：选修人数 > 5 的课程统计（不重要）

① 题目本质：聚合过滤 + 多字段排序

② 解题关键：HAVING cnt > 5；ORDER BY cnt DESC, c_id ASC

③ 核心 SQL

SELECT c_id, COUNT(s_id) cnt
FROM Score
GROUP BY c_id
HAVING cnt > 5
ORDER BY cnt DESC, c_id ASC;

④ 可能的坑：HAVING 可以引用 SELECT 别名（MySQL 允许），但标准 SQL 不保证

第 44 题：至少选修两门课的学生（不重要）

① 题目本质：分组计数 >= 2

② 解题关键：HAVING COUNT(c_id) >= 2

③ 核心 SQL

SELECT s_id, COUNT(c_id) cnt
FROM Score
GROUP BY s_id
HAVING cnt >= 2;

④ 可能的坑：同上

第 45 题：选修了全部课程的学生（重点）⭐

① 题目本质：选课数 = 总课程数

② 解题关键：子查询统计每人选课数，与 COUNT(*) FROM Course 相等则入选

③ 核心 SQL

SELECT s.*
FROM Student s
         INNER JOIN (SELECT s_id
                     FROM Score
                     GROUP BY s_id
                     HAVING COUNT(s_id) = (SELECT COUNT(*) FROM Course)) scc
                    ON s.s_id = scc.s_id;

④ 可能的坑：INNER JOIN 取交集，天然排除未选课学生

第 46 题：查询各学生年龄（日期函数）

① 题目本质：日期计算

② 解题关键：TIMESTAMPDIFF(YEAR, s_birth, CURDATE()) 是最精确的年龄算法

③ 核心 SQL

-- 推荐写法
SELECT s_name, s_birth,
       TIMESTAMPDIFF(YEAR, s_birth, CURDATE()) age
FROM Student;

-- 身份证号计算年龄（进阶）
SELECT id_card,
       TIMESTAMPDIFF(YEAR,
           CASE
               WHEN LENGTH(id_card) = 18 THEN STR_TO_DATE(SUBSTR(id_card, 7, 8), '%Y%m%d')
               WHEN LENGTH(id_card) = 15 THEN STR_TO_DATE(CONCAT('19', SUBSTR(id_card, 7, 6)), '%Y%m%d')
           END,
           CURDATE()) age
FROM Student;

④ 可能的坑

第 47 题：没学过张三任一门课的学生（自己写的）

① 题目本质：没有与张三任何课程有交集

② 解题关键：NOT EXISTS 双层嵌套——外层遍历学生选课，内层判断是否是张三的课

③ 核心 SQL

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

④ 可能的坑

第 48 题：两门以上不及格的学生（自己写的）

① 题目本质：同第 15 题变体

② 解题关键：HAVING SUM(s_score < 60) > 2

③ 核心 SQL

SELECT s_id, ROUND(AVG(s_score), 2) avg_score
FROM Score
GROUP BY s_id
HAVING SUM(s_score < 60) > 2;

④ 可能的坑：注意 > 2 是超过两门（即 3 门及以上），题意需要 >= 2 还是 > 2 看题

第 49 题：本月过生日的学生（日期函数）

① 题目本质：月份匹配

② 解题关键：MONTH(s_birth) = MONTH(CURDATE())

③ 核心 SQL

SELECT s_name, s_birth
FROM Student
WHERE MONTH(s_birth) = MONTH(CURDATE());

④ 可能的坑：DISTINCT 对整行去重，不能只对某一列去重

第 50 题：下周过生日的学生（日期函数进阶）

① 题目本质：7 天内的生日匹配，需处理跨年边界

② 解题关键：将生日换成今年日期后 BETWEEN curdate AND curdate + 7；跨年再加一个明年的判断

③ 核心 SQL

SELECT s_name, s_birth
FROM Student
WHERE STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', DATE_FORMAT(s_birth, '%m-%d')), '%Y-%m-%d')
          BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
   OR STR_TO_DATE(CONCAT(YEAR(CURDATE()) + 1, '-', DATE_FORMAT(s_birth, '%m-%d')), '%Y-%m-%d')
          BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

下个月过生日：

SELECT s_name, s_birth
FROM Student
WHERE MONTH(s_birth) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));

④ 可能的坑

想一想：为什么不能直接用 s_birth BETWEEN CURDATE() AND CURDATE()+7？为什么还要 OR 一个「明年」分支？

因为 s_birth 存的是出生年月日，可能是几十年前的日期，直接和今年的日期比永远落在区间外。正确做法是先把它「平移」到今年再比；而 12 月底过生日的人，今年的日期已经过了，只能落到明年，所以要再加一个 YEAR + 1 的分支兜住跨年。

附录：核心规律速查

场景推荐方式
存在/不存在EXISTS / NOT EXISTS
集合包含比较NOT EXISTS (NOT EXISTS ...)
集合大小比较COUNT(DISTINCT ...)
分组内排名ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)
行转列MAX(CASE WHEN ... THEN ... END) + GROUP BY
分区间统计SUM(CASE WHEN ... THEN 1 ELSE 0 END)
Top1ORDER BY DESC LIMIT 1
TopN窗口函数 + 外层 WHERE rk <= N
年龄计算TIMESTAMPDIFF(YEAR, birth, CURDATE())
字符串集合比较GROUP_CONCAT(... ORDER BY ... SEPARATOR ',')
多字段排序ORDER BY a DESC, b ASC（靠前优先级高）

IN vs EXISTS 黄金法则：子查询结果可能含 NULL 时一律用 EXISTS；外表大内表小用 IN，外表小内表大用 EXISTS；能用 INNER JOIN ON 就不用 IN。

章末提问

1. 「NOT IN 子查询里为什么一旦出现 NULL 结果就整表为空？怎么规避？」

结论：因为 x NOT IN (1, NULL) 展开成 x != 1 AND x != NULL，而 x != NULL 恒为未知，WHERE 不保留未知行，所以整表被过滤空。规避：对可能含 NULL 的子查询一律改用 NOT EXISTS。

2. 「WHERE 和 HAVING 到底差在哪？为什么聚合条件不能写 WHERE？」

结论：WHERE 在分组前逐行过滤，HAVING 在分组后对聚合结果过滤。因为 AVG/SUM 这类聚合值只有在 GROUP BY 之后才存在，分组前无从比较，所以过滤聚合结果只能用 HAVING。

3. 「ROW_NUMBER、RANK、DENSE_RANK 三者区别？并列成绩时该用哪个？」

结论：ROW_NUMBER 连续编号、并列被拆开；RANK 并列同号且下一名跳号；DENSE_RANK 并列同号但不跳号。要并列同名次用 RANK/DENSE_RANK，要严格取固定人数用 ROW_NUMBER。

4. 「行转列为什么 CASE WHEN 必须配 MAX？不配会怎样？」

结论：因为 GROUP BY 后每个学生只剩一行，而 CASE WHEN 对每个学生只在「该门课」那行有值、其余为 NULL，必须靠 MAX 在一堆 NULL 里挑出唯一真实值压成列。不配聚合会触发 only_full_group_by 报错或散成多行。

5. 「COUNT(*) 和 COUNT(列) 有什么区别？」

结论：COUNT(*) 数行数、把 NULL 行也算进去；COUNT(列) 数该列非 NULL 的值个数。左连接补出来的 NULL 行，用 COUNT(*) 会误算，必须用 COUNT(具体列)。

Share this post on:
Share this post via WhatsAppShare this post on FacebookShare this post on XShare this post via TelegramShare this post on PinterestShare this post via email
Previous Post
Rand5生成Rand7——拒绝采样的原理与应用
Next Post
其他问题——智力题、大数据处理、SQL 题