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

作者

MySQL 常见 SQL 面试题 2

本文档是 《MySQL 常见 SQL 面试题》 的续篇，侧重进阶题型和综合应用：排名/TOP N、窗口函数、自连接、行转列、去重、NULL 处理、UNION、复杂子查询等。每题都给出题目、思路和完整示例 SQL，适合在掌握基础题后继续刷题。

建议：先完成《常见 SQL 面试题》中的基础题，再用本文档练习。示例仍基于 students、courses、scores 三张表（结构见下文「表结构回顾」）。

目录

  1. 表结构回顾与补充数据
  2. 排名与 TOP N 题
  3. 行转列与列转行（透视）
  4. 去重与重复数据题
  5. 自连接题
  6. NULL 处理与条件分支题
  7. UNION 与多结果集合并
  8. 复杂子查询与 EXISTS
  9. 日期与时间范围题
  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. 排名与 TOP N 题

2.1 查询每门课程成绩最高的学生（姓名、课程名、分数）

思路：先按课程分组得到每门课的最高分，再和成绩表、学生表、课程表关联，匹配“该学生该课程分数 = 该课程最高分”。

SELECT s.name AS 姓名, c.name AS 课程, sc.score AS 分数
FROM scores sc
JOIN students s ON sc.student_id = s.id
JOIN courses c ON sc.course_id = c.id
JOIN (
    SELECT course_id, MAX(score) AS max_score
    FROM scores
    WHERE score IS NOT NULL
    GROUP BY course_id
) t ON sc.course_id = t.course_id AND sc.score = t.max_score
ORDER BY c.id;

说明：若同一门课有两人同分且都是最高分，会都显示；若要求只显示一人，可用窗口函数（见下）或再按 id 取一条。

2.2 查询每个学生分数最高的那一门课（姓名、课程名、分数）

思路：先按学生分组得到每人最高分，再和 scores、courses 关联。

SELECT s.name AS 姓名, c.name AS 课程, t.max_score AS 分数
FROM students s
JOIN (
    SELECT student_id, MAX(score) AS max_score
    FROM scores
    WHERE score IS NOT NULL
    GROUP BY student_id
) t ON s.id = t.student_id
JOIN scores sc ON sc.student_id = t.student_id AND sc.score = t.max_score
JOIN courses c ON sc.course_id = c.id
ORDER BY s.id;

注意：若同一学生有多门课同分且都是最高，会多行；若要唯一一行可加 LIMIT 1 子查询或窗口函数。

2.3 查询每个班级年龄最大的学生（姓名、班级、年龄）

思路：先按班级求最大年龄，再和学生表匹配。

SELECT s.name AS 姓名, s.class_name AS 班级, s.age AS 年龄
FROM students s
JOIN (
    SELECT class_name, MAX(age) AS max_age
    FROM students
    GROUP BY class_name
) t ON s.class_name = t.class_name AND s.age = t.max_age
ORDER BY s.class_name;

2.4 使用窗口函数：每个学生按总分排名（MySQL 8.0+）

题目：计算每个学生的总分，并按总分降序排名（相同总分并列同一名次，下一名次不连续）。

窗口函数：RANK() OVER (ORDER BY 总分 DESC)。

SELECT name AS 姓名,
       total AS 总分,
       RANK() OVER (ORDER BY total DESC) AS 排名
FROM (
    SELECT s.id, s.name, SUM(sc.score) AS total
    FROM students s
    JOIN scores sc ON s.id = sc.student_id
    GROUP BY s.id, s.name
) t;

若要求“相同分数并列且下一名次连续”，用 DENSE_RANK()：

SELECT name AS 姓名,
       total AS 总分,
       DENSE_RANK() OVER (ORDER BY total DESC) AS 排名
FROM (
    SELECT s.id, s.name, SUM(sc.score) AS total
    FROM students s
    JOIN scores sc ON s.id = sc.student_id
    GROUP BY s.id, s.name
) t;

若要求“连续序号、同分也不同名次”，用 ROW_NUMBER()：

SELECT name AS 姓名,
       total AS 总分,
       ROW_NUMBER() OVER (ORDER BY total DESC) AS 序号
FROM (
    SELECT s.id, s.name, SUM(sc.score) AS total
    FROM students s
    JOIN scores sc ON s.id = sc.student_id
    GROUP BY s.id, s.name
) t;

2.5 每个班级内按年龄排名（MySQL 8.0+）

题目：在每个班级内部按年龄从大到小排名。

SELECT name AS 姓名,
       class_name AS 班级,
       age AS 年龄,
       RANK() OVER (PARTITION BY class_name ORDER BY age DESC) AS 班内排名
FROM students
ORDER BY class_name, 班内排名;

考点：PARTITION BY class_name 表示“按班级分区”，在各自分区内再 ORDER BY age DESC 排名。

3. 行转列与列转行（透视）

3.1 行转列：每个学生的语文、数学、英语成绩各一列

题目：结果形如：姓名 | 语文 | 数学 | 英语。

写法一：MAX + CASE WHEN（兼容 MySQL 5.x）

SELECT s.name AS 姓名,
       MAX(CASE WHEN c.name = '语文' THEN sc.score END) AS 语文,
       MAX(CASE WHEN c.name = '数学' THEN sc.score END) AS 数学,
       MAX(CASE WHEN c.name = '英语' THEN sc.score END) 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;

说明：LEFT JOIN 保证没选课的学生也出现，成绩列为 NULL；用 MAX 是因为按学生分组后每门课只有一行，MAX 只是“取唯一值”。

3.2 行转列：并带总分、平均分

在 3.1 基础上加 SUM、AVG：

SELECT s.name AS 姓名,
       MAX(CASE WHEN c.name = '语文' THEN sc.score END) AS 语文,
       MAX(CASE WHEN c.name = '数学' THEN sc.score END) AS 数学,
       MAX(CASE WHEN c.name = '英语' THEN sc.score END) AS 英语,
       SUM(sc.score) AS 总分,
       AVG(sc.score) 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;

4. 去重与重复数据题

4.1 查询有重复成绩的学生（同一学生同一课程考了多次）

假设 scores 表允许同一 (student_id, course_id) 有多条记录（例如多次考试）。要找出“存在重复 (student_id, course_id)”的学生。

SELECT DISTINCT student_id
FROM scores
GROUP BY student_id, course_id
HAVING COUNT(*) > 1;

若要显示学生姓名：

SELECT DISTINCT s.name
FROM students s
JOIN (
    SELECT student_id
    FROM scores
    GROUP BY student_id, course_id
    HAVING COUNT(*) > 1
) t ON s.id = t.student_id;

4.2 只保留每个学生每门课最高分的那一条记录（删除重复中分数较低的行）

思路：先找出“要保留”的 (id) 或“要删”的 (id)，再 DELETE。若用子查询删除同一张表，MySQL 可能报错，常用 JOIN 写法：

-- 删除“非最高分”的记录（保留每组 student_id, course_id 下 score 最大的一条）
DELETE sc FROM scores sc
JOIN (
    SELECT student_id, course_id, MAX(score) AS max_score
    FROM scores
    GROUP BY student_id, course_id
    HAVING COUNT(*) > 1
) t ON sc.student_id = t.student_id AND sc.course_id = t.course_id AND sc.score < t.max_score;

注意：实际表若无重复可先备份或跳过执行；这里仅作语法与思路示例。

5. 自连接题

5.1 查询同班同学配对（每对只显示一次，如 A-B 不再显示 B-A）

题目：每个学生列出其同班另一名同学，且 (A, B) 与 (B, A) 只保留一组（如按 id 小在前）。

SELECT s1.name AS 学生A, s2.name AS 学生B
FROM students s1
JOIN students s2 ON s1.class_name = s2.class_name AND s1.id < s2.id
ORDER BY s1.id, s2.id;

说明：s1.id < s2.id 保证配对不重复且自己不和自己配对。

5.2 查询年龄相同的学生对（姓名、年龄，且 id 小在前）

SELECT s1.name AS 学生A, s2.name AS 学生B, s1.age AS 年龄
FROM students s1
JOIN students s2 ON s1.age = s2.age AND s1.id < s2.id
ORDER BY s1.age, s1.id;

6. NULL 处理与条件分支题

6.1 成绩列：NULL 显示为 0，并计算总分

SELECT s.name,
       IFNULL(sc.score, 0) AS 分数,
       SUM(IFNULL(sc.score, 0)) OVER (PARTITION BY s.id) AS 总分
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id;

若不用窗口函数，可先分组再 JOIN：

SELECT s.name,
       sc.course_id,
       IFNULL(sc.score, 0) AS 分数
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id;
-- 总分需另写一查询按 student_id SUM(IFNULL(score,0))

6.2 按分数段打等级：优秀(≥90)、良好(80~89)、及格(60~79)、不及格(<60)

SELECT s.name AS 姓名,
       c.name AS 课程,
       sc.score AS 分数,
       CASE
           WHEN sc.score IS NULL THEN '缺考'
           WHEN sc.score >= 90 THEN '优秀'
           WHEN sc.score >= 80 THEN '良好'
           WHEN sc.score >= 60 THEN '及格'
           ELSE '不及格'
       END AS 等级
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
ORDER BY s.id, c.id;

6.3 统计各等级人数（优秀/良好/及格/不及格各多少人）

SELECT
    CASE
        WHEN score IS NULL THEN '缺考'
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS 等级,
    COUNT(*) AS 人数
FROM scores
GROUP BY
    CASE
        WHEN score IS NULL THEN '缺考'
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END;

MySQL 中可对“等级”用数字或字母编码再 GROUP BY，避免重复写 CASE；这里为可读性直接按 CASE 分组。

7. UNION 与多结果集合并

7.1 查询“语文成绩高于 85 或数学成绩高于 85”的学生（去重）

题目：同一学生只要满足其一即出现一次。

SELECT student_id FROM scores sc JOIN courses c ON sc.course_id = c.id WHERE c.name = '语文' AND sc.score > 85
UNION
SELECT student_id FROM scores sc JOIN courses c ON sc.course_id = c.id WHERE c.name = '数学' AND sc.score > 85;

UNION 会去重；若需要保留重复（同一学生满足两次也显示两次）用 UNION ALL。

7.2 分别统计“男生人数”和“女生人数”，结果两行（性别、人数）

SELECT '男' AS 性别, COUNT(*) AS 人数 FROM students WHERE gender = '男'
UNION ALL
SELECT '女', COUNT(*) FROM students WHERE gender = '女';

8. 复杂子查询与 EXISTS

8.1 查询“至少选修了学号为 1 的学生所选修的全部课程”的学生（除自己外）

题目：即：不存在“学号 1 选了但该学生没选”的课程。

思路：对每个学生 s，用 NOT EXISTS：不存在这样一门课 c，学号 1 选了而 s 没选。

SELECT DISTINCT s.id, s.name
FROM students s
JOIN scores sc ON s.id = sc.student_id
WHERE s.id != 1
  AND NOT EXISTS (
      SELECT 1
      FROM scores s1
      WHERE s1.student_id = 1
        AND NOT EXISTS (
            SELECT 1
            FROM scores s2
            WHERE s2.student_id = s.id AND s2.course_id = s1.course_id
        )
  );

说明：内层 NOT EXISTS 表示“学号 1 选的某门课，当前学生 s 没选”；外层 NOT EXISTS 表示“不存在这样的课”，即 s 选了学号 1 选的所有课。

8.2 查询“语文成绩高于数学成绩”的学生（姓名、语文分、数学分）

思路：每个学生取语文一行、数学一行，再比较。可用自连接或子查询。

SELECT s.name AS 姓名,
       a.score AS 语文,
       b.score AS 数学
FROM students s
JOIN scores a ON s.id = a.student_id
JOIN courses c1 ON a.course_id = c1.id AND c1.name = '语文'
JOIN scores b ON s.id = b.student_id
JOIN courses c2 ON b.course_id = c2.id AND c2.name = '数学'
WHERE a.score > b.score;

9. 日期与时间范围题

9.1 查询某次考试日期（如 2025-01-10）各科平均分

SELECT c.name AS 课程, AVG(sc.score) AS 平均分
FROM scores sc
JOIN courses c ON sc.course_id = c.id
WHERE sc.exam_date = '2025-01-10'
GROUP BY c.id, c.name;

9.2 查询最近一次考试（按 exam_date 最大）的每个学生总分

SELECT s.name, SUM(sc.score) AS 总分
FROM students s
JOIN scores sc ON s.id = sc.student_id
WHERE sc.exam_date = (SELECT MAX(exam_date) FROM scores)
GROUP BY s.id, s.name;

9.3 查询“连续两天都有考试记录”的日期对（示例）

若 scores 表有多次考试日期，可先列出日期，再自连接找“相差一天”的日期对（此处仅作思路示例，表内需有多个不同 exam_date）：

SELECT DISTINCT a.exam_date AS 日期1, b.exam_date AS 日期2
FROM (SELECT DISTINCT exam_date FROM scores) a
JOIN (SELECT DISTINCT exam_date FROM scores) b
ON DATEDIFF(b.exam_date, a.exam_date) = 1;

10. 综合实战题

10.1 每个班级总分第一的学生（姓名、班级、总分）

思路：先按班级求每人总分，再按班级分组取每组最高总分，再关联回学生和总分。

SELECT t.class_name AS 班级, s.name AS 姓名, t.total AS 总分
FROM (
    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
) t
JOIN (
    SELECT class_name, MAX(total) AS max_total
    FROM (
        SELECT 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.class_name
    ) x
    GROUP BY class_name
) m ON t.class_name = m.class_name AND t.total = m.max_total
JOIN students s ON t.id = s.id
ORDER BY t.class_name;

简化写法（先建“每人总分”再按班取最大）：

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
)
SELECT st.name AS 姓名, st.class_name AS 班级, st.total AS 总分
FROM stu_total st
JOIN (
    SELECT class_name, MAX(total) AS max_total
    FROM stu_total
    GROUP BY class_name
) t ON st.class_name = t.class_name AND st.total = t.max_total
ORDER BY st.class_name;

（MySQL 8.0+ 支持 WITH；5.7 可用内联子查询代替 WITH。）

10.2 查询“至少有一门课成绩高于该科平均分”的学生

思路：对每个学生每条成绩，判断该科平均分；若存在一门 score > 该科平均分即可。

SELECT DISTINCT s.id, s.name
FROM students s
JOIN scores sc ON s.id = sc.student_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
WHERE sc.score > t.avg_score;

10.3 成绩表：每门课比上一条记录（按 id）高多少分（同一学生）

题目：按 student_id、course_id 分组，按 id 排序，当前行分数减去“上一行”分数（MySQL 8.0 用 LAG）。

SELECT student_id,
       course_id,
       score,
       LAG(score) OVER (PARTITION BY student_id, course_id ORDER BY id) AS 上一行分数,
       score - LAG(score) OVER (PARTITION BY student_id, course_id ORDER BY id) AS 分数差
FROM scores;

11. 易错点与书写规范

  1. UPDATE/DELETE 务必带 WHERE，避免误改/误删全表。
  2. JOIN 必须写 ON，否则是笛卡尔积。
  3. GROUP BY 时，SELECT 中非聚合列尽量都出现在 GROUP BY 中（严格模式要求）。
  4. NULL 判断用 IS NULL / IS NOT NULL，不用 = NULL。
  5. 子查询删除/更新同一张表时，用 JOIN 或派生表避免“同一表同时读写的限制”。
  6. UNION 去重、UNION ALL 不去重；两边的列数、类型要对应。
  7. 窗口函数 OVER (PARTITION BY … ORDER BY …) 中，ORDER BY 决定窗口内顺序；是否有 PARTITION BY 决定“全局”还是“分区内”计算。

建议在本地建好 students、courses、scores 后，按章节逐题敲一遍 SQL 并执行，再尝试自己变形（如改成分数、班级、日期条件）。配合《常见 SQL 面试题》一起使用，可覆盖大部分初中级 SQL 笔试与面试题。

访问数: 16

发表评论