mysql数据库的常见SQL面试题3

作者

MySQL 常见 SQL 面试题 3

本文档是 《常见 SQL 面试题》 和 《常见 SQL 面试题 2》 的第三篇，侧重：分页、字符串聚合（GROUP_CONCAT）、缺口/缺失值、累计与汇总（ROLLUP）、“未选课”类全组合、简单递归与变量 等题型。每题都给出题目、思路和完整示例 SQL，适合在掌握前两篇后继续进阶。

建议：先完成前两篇的基础与进阶题，再用本文档练习。示例仍基于 students、courses、scores 三张表（结构见下文）。

目录

  1. 表结构速查
  2. 分页查询题
  3. 字符串聚合与 GROUP_CONCAT
  4. 缺口与缺失值题
  5. 累计与汇总（ROLLUP / 小计行）
  6. “未选课”与全组合题
  7. 多列 IN 与元组比较
  8. 变量与模拟行号（MySQL 5.x）
  9. 递归 CTE 入门（MySQL 8.0+）
  10. 综合业务场景题
  11. 书写与性能注意

1. 表结构速查

与前面两篇一致，三张表如下（若你本地已有可跳过建表）。

CREATE TABLE IF NOT EXISTS students (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(50) NOT NULL,
    gender      CHAR(1) DEFAULT '男',
    age         INT,
    class_name  VARCHAR(20)
);

CREATE TABLE IF NOT EXISTS courses (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS scores (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    student_id  INT NOT NULL,
    course_id   INT NOT NULL,
    score       DECIMAL(5,2),
    exam_date   DATE
);

-- 示例数据（可按需插入）
INSERT INTO students (name, gender, age, class_name) VALUES
('张三', '男', 18, '高一1班'), ('李四', '女', 17, '高一2班'),
('王五', '男', 18, '高一1班'), ('赵六', '女', 17, '高一2班'),
('钱七', '男', 19, '高一1班'), ('孙八', '女', 18, '高一2班');

INSERT INTO courses (name) VALUES ('语文'), ('数学'), ('英语');

INSERT INTO scores (student_id, course_id, score, exam_date) VALUES
(1,1,85,'2025-01-10'),(1,2,92,'2025-01-11'),(1,3,78,'2025-01-12'),
(2,1,88,'2025-01-10'),(2,2,76,'2025-01-11'),(2,3,90,'2025-01-12'),
(3,1,72,'2025-01-10'),(3,2,85,'2025-01-11'),(3,3,88,'2025-01-12'),
(4,1,90,'2025-01-10'),(4,2,82,'2025-01-11'),
(5,1,95,'2025-01-10'),(5,2,88,'2025-01-11'),
(6,1,80,'2025-01-10'),(6,2,NULL,'2025-01-11');

2. 分页查询题

2.1 基本分页：第 2 页，每页 3 条（按 id 升序）

题目：学生表按 id 排序，取第 2 页（即第 4、5、6 条）。

公式：第 page 页（从 1 开始）、每页 page_size 条：
*跳过条数 = (page – 1) page_size，取条数 = page_size**。

-- 第 2 页，每页 3 条 → 跳过 3 条，取 3 条
SELECT * FROM students
ORDER BY id
LIMIT 3, 3;

或使用 LIMIT 条数 OFFSET 跳过（语义更清晰）：

SELECT * FROM students
ORDER BY id
LIMIT 3 OFFSET 3;

2.2 分页并显示“当前第几页、共几页、总条数”

思路：分页数据用 LIMIT 取；总条数单独查一次（或用窗口函数 MySQL 8.0+ 一次查，但总条数要算总数）。

示例：查第 2 页且每页 3 条，并知道总共有多少条、多少页

-- 1）总条数
SELECT COUNT(*) AS 总条数 FROM students;
-- 假设得到 6，每页 3 条则共 2 页

-- 2）第 2 页数据
SELECT * FROM students ORDER BY id LIMIT 3 OFFSET 3;

应用层通常：先执行 COUNT(*) 得到总条数，算出总页数，再执行带 LIMIT/OFFSET 的查询取当前页数据。

2.3 大偏移量分页优化思路（面试常问）

题目：表有 100 万行，要查第 10 万页（每页 10 条），用 LIMIT 999990, 10 会很慢，为什么？怎么优化？

原因简述：
LIMIT 999990, 10 会让 MySQL 先扫描并“跳过”前 999990 行，再返回 10 行，代价很大。

常见优化思路（口述即可，不必死记实现）：

  1. 延迟关联：先在内层用索引查出当前页的 id（只查 id，走索引），再根据这批 id 回表查完整行。
    例如：SELECT * FROM students WHERE id IN (SELECT id FROM students ORDER BY id LIMIT 10 OFFSET 999990) ORDER BY id;
    （具体语法随版本和索引可能略有不同，思路是“先拿 id 再回表”。）
  2. 记住上一页最后一条的 id：
    第 2 页不用 LIMIT 10 OFFSET 10，而用 WHERE id > 上一页最后一条id ORDER BY id LIMIT 10，避免大 OFFSET。
  3. 业务上限制深度分页：
    不允许直接跳到很后面的页，只提供“上一页 / 下一页”，用 id 或时间游标翻页。

3. 字符串聚合与 GROUP_CONCAT

3.1 每个学生选了的课程名拼成一行（逗号分隔）

题目：结果形如：张三 → 语文,数学,英语；李四 → 语文,数学,英语。

SELECT s.name AS 姓名,
       GROUP_CONCAT(c.name ORDER BY c.id SEPARATOR '，') AS 所选课程
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
LEFT JOIN courses c ON sc.course_id = c.id
GROUP BY s.id, s.name
ORDER BY s.id;

说明：

  • GROUP_CONCAT(列 [ORDER BY …] [SEPARATOR ‘分隔符’])：把该组内该列的值用分隔符拼成一个字符串。
  • 默认分隔符是逗号 ,，这里用中文逗号 ， 更易读。
  • ORDER BY c.id 保证课程顺序一致。

3.2 每个学生：课程名与成绩成对显示（如“语文:85, 数学:92”）

SELECT s.name AS 姓名,
       GROUP_CONCAT(
           CONCAT(c.name, ':', IFNULL(sc.score, '缺考'))
           ORDER BY c.id
           SEPARATOR '，'
       ) AS 成绩明细
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
LEFT JOIN courses c ON sc.course_id = c.id
GROUP BY s.id, s.name
ORDER BY s.id;

3.3 每个班级的学生名单拼成一行

SELECT class_name AS 班级,
       GROUP_CONCAT(name ORDER BY id SEPARATOR '、') AS 学生名单
FROM students
GROUP BY class_name;

3.4 GROUP_CONCAT 长度限制（了解）

系统变量：group_concat_max_len 默认约 1024 字节，拼接结果超过会被截断。
若需要很长拼接，可在会话中设置：
SET SESSION group_concat_max_len = 10000;
（具体数值按需设置。）

4. 缺口与缺失值题

4.1 找出“学号连续但缺失”的 id（表中没有的 id）

题目：students 表 id 为 1,2,3,4,5,6，若缺少 2、4，要找出 2、4。
思路：用一张“应有 id 的序列”和实际表 LEFT JOIN，实际表为 NULL 的即为缺失。

方法一：用递归 CTE 生成 1～N 的序列（MySQL 8.0+）

WITH RECURSIVE num AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM num WHERE n < (SELECT MAX(id) FROM students)
)
SELECT num.n AS 缺失的id
FROM num
LEFT JOIN students s ON num.n = s.id
WHERE s.id IS NULL
ORDER BY num.n;

方法二：若已知 id 范围且不大，可用已有表自连接“造序列”
（例如用一张足够大的序列表或数字表，这里仅作思路提示。）

4.2 找出“没有成绩记录”的学生（再次巩固）

SELECT s.*
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
WHERE sc.id IS NULL;

或：

SELECT * FROM students
WHERE id NOT IN (SELECT student_id FROM scores WHERE student_id IS NOT NULL);

（NOT IN 时注意子查询里不要有 NULL，否则结果会不符合预期。）

5. 累计与汇总（ROLLUP / 小计行）

5.1 按班级统计人数，并加一行“总计”

题目：每班一行人数，最后多一行“班级=NULL、人数=总人数”。

SELECT class_name AS 班级, COUNT(*) AS 人数
FROM students
GROUP BY class_name WITH ROLLUP;

说明：WITH ROLLUP 会在分组结果后增加“小计/总计”行，被 ROLLUP 的维度列在该行中为 NULL。

5.2 按班级、性别统计人数，并带班级小计和总计

SELECT class_name AS 班级, gender AS 性别, COUNT(*) AS 人数
FROM students
GROUP BY class_name, gender WITH ROLLUP
ORDER BY class_name, gender;

结果中会出现：

  • 每个 (班级, 性别) 一行；
  • 每个班级一行小计（性别为 NULL）；
  • 最后一行总计（班级、性别都为 NULL）。

5.3 给 ROLLUP 产生的 NULL 起别名（如“合计”）

SELECT
    IFNULL(class_name, '合计') AS 班级,
    IFNULL(gender, '小计') AS 性别,
    COUNT(*) AS 人数
FROM students
GROUP BY class_name, gender WITH ROLLUP
ORDER BY class_name, gender;

（若“合计/小计”要更精细区分，可用 GROUPING(列) 判断是否为 ROLLUP 产生的 NULL，再配合 CASE 显示不同文案，此处从简。）

6. “未选课”与全组合题

6.1 列出“每个学生 + 每门课”，若已选则显示分数，未选则显示“未选”

思路：学生 × 课程 做笛卡尔积（CROSS JOIN），再 LEFT JOIN 成绩表。

SELECT s.name AS 姓名,
       c.name AS 课程,
       IFNULL(CAST(sc.score AS CHAR), '未选') AS 分数
FROM students s
CROSS JOIN courses c
LEFT JOIN scores sc ON sc.student_id = s.id AND sc.course_id = c.id
ORDER BY s.id, c.id;

6.2 找出“没选数学”的学生

SELECT s.*
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
LEFT JOIN courses c ON sc.course_id = c.id AND c.name = '数学'
WHERE c.id IS NULL;

或 NOT EXISTS：

SELECT s.*
FROM students s
WHERE NOT EXISTS (
    SELECT 1 FROM scores sc
    JOIN courses c ON sc.course_id = c.id
    WHERE sc.student_id = s.id AND c.name = '数学'
);

7. 多列 IN 与元组比较

7.1 查询“(班级, 年龄)”在指定列表中的学生

题目：找出 (class_name, age) 属于 (‘高一1班’, 18)、(‘高一2班’, 17) 的学生。

SELECT * FROM students
WHERE (class_name, age) IN (
    ('高一1班', 18),
    ('高一2班', 17)
);

说明：(a, b) IN ((x1,y1), (x2,y2)) 表示 (a,b) 等于 (x1,y1) 或 (x2,y2)。

7.2 子查询返回多列时用元组 IN

例如：找出“班级和年龄与学号为 1 的学生相同”的其他学生（排除自己）。

SELECT * FROM students
WHERE (class_name, age) = (SELECT class_name, age FROM students WHERE id = 1)
  AND id != 1;

8. 变量与模拟行号（MySQL 5.x）

8.1 用用户变量给结果加“行号”（MySQL 5.x 无 ROW_NUMBER 时）

注意：MySQL 8.0 后更推荐用 ROW_NUMBER()；变量在 8.0 中排序可能不稳定，这里仅作面试/兼容性了解。

SET @row_num = 0;
SELECT @row_num := @row_num + 1 AS 行号, id, name, class_name
FROM students
ORDER BY id;

说明：@row_num := @row_num + 1 在每行执行时自增，从而得到 1,2,3,… 的行号。

8.2 按班级分组给组内行号（模拟 PARTITION BY + ROW_NUMBER）

SET @row_num = 0;
SET @class = '';
SELECT
    name,
    class_name,
    @row_num := IF(@class = class_name, @row_num + 1, 1) AS 班内序号,
    @class := class_name AS _
FROM students
ORDER BY class_name, id;

（8.0+ 直接用 ROW_NUMBER() OVER (PARTITION BY class_name ORDER BY id) 更清晰。）

9. 递归 CTE 入门（MySQL 8.0+）

9.1 生成 1 到 10 的数字序列

WITH RECURSIVE num AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM num WHERE n < 10
)
SELECT * FROM num;

结构：

  • 锚点：SELECT 1 AS n；
  • 递归：SELECT n + 1 FROM num WHERE n < 10；
  • 用 UNION ALL 不断产生下一行直到条件不满足。

9.2 用递归序列生成“连续日期”（如最近 7 天）

WITH RECURSIVE d AS (
    SELECT CURDATE() AS dt
    UNION ALL
    SELECT dt - INTERVAL 1 DAY FROM d WHERE dt > CURDATE() - INTERVAL 6 DAY
)
SELECT dt FROM d ORDER BY dt;

（这里生成的是“从 6 天前到今天”的 7 个日期，可按需改区间。）

10. 综合业务场景题

10.1 每个班级的“总分第一名”且“若有并列都显示”

WITH stu_total AS (
    SELECT s.id, s.name, s.class_name, SUM(sc.score) AS total
    FROM students s
    JOIN scores sc ON s.id = sc.student_id
    GROUP BY s.id, s.name, s.class_name
),
class_max AS (
    SELECT class_name, MAX(total) AS max_total
    FROM stu_total
    GROUP BY class_name
)
SELECT st.name, st.class_name, st.total AS 总分
FROM stu_total st
JOIN class_max cm ON st.class_name = cm.class_name AND st.total = cm.max_total
ORDER BY st.class_name, st.total DESC;

10.2 每门课“成绩高于该科平均分”的学生人数

SELECT c.name AS 课程,
       COUNT(*) AS 人数
FROM scores sc
JOIN courses c ON sc.course_id = c.id
JOIN (SELECT course_id, AVG(score) AS avg_score FROM scores WHERE score IS NOT NULL GROUP BY course_id) t
  ON sc.course_id = t.course_id AND sc.score > t.avg_score
GROUP BY c.id, c.name;

10.3 学生选课门数分布：选 1 门、2 门、3 门各多少人

SELECT 选课门数, COUNT(*) AS 学生数
FROM (
    SELECT student_id, COUNT(DISTINCT course_id) AS 选课门数
    FROM scores
    GROUP BY student_id
) t
GROUP BY 选课门数
ORDER BY 选课门数;

11. 书写与性能注意

  1. 分页：大 OFFSET 尽量用“基于 id/时间游标”的方式替代。
  2. GROUP_CONCAT：注意 group_concat_max_len，结果过长会被截断。
  3. WITH ROLLUP：产生的汇总行中，对应维度列为 NULL，用 IFNULL/GROUPING 区分显示。
  4. CROSS JOIN：数据量大时“全组合”行数会很多，只在小表或必要场景使用。
  5. 递归 CTE：必须有终止条件，避免无限递归；MySQL 有递归深度限制。
  6. 用户变量：在 8.0 中与 ORDER BY 等组合时行为可能变化，排序相关需求优先用窗口函数。

建议在本地用 students、courses、scores 把每题都跑一遍，再尝试改条件（班级、课程、分页大小等）。配合《常见 SQL 面试题》和《常见 SQL 面试题 2》，可覆盖大部分初中级 SQL 笔试与面试中的分页、聚合、缺口、ROLLUP、递归等考点。

访问数: 20

发表评论