1. 场景介绍
假设我们正在为一个在线教育平台构建后台，需要分页展示所有课程，并且每展示一门课程，都要同时列出选修了该课程的所有学生。这是一个典型的多对多关系。
2. 表结构与测试数据
我们先创建三张表并插入一些示例数据。
表 1: courses (课程表)
存储课程的基本信息。
CREATE TABLE courses (
course_id INT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
create_time TIMESTAMP NOT NULL
);
-- 插入测试数据
INSERT INTO courses (course_id, title, create_time) VALUES
(101, '基础数学', '2023-01-01 10:00:00'),
(102, '大学英语', '2023-01-02 11:00:00'), -- 这是一门热门课程，有很多学生选修
(103, '高等物理', '2023-01-03 09:00:00'),
(104, '编程入门', '2023-01-04 14:00:00');
表 2: students (学生表)
存储学生的基本信息。
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
-- 插入测试数据
INSERT INTO students (student_id, name) VALUES
(1, '小明'),
(2, '小红'),
(3, '小刚'),
(4, '小丽'),
(5, '小强');
表 3: student_courses (学生选课表)
这是关联表，表示学生和课程之间的多对多关系。
CREATE TABLE student_courses (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
-- 插入测试数据：模拟选课情况
-- ‘大学英语’ (102) 有3个学生选修，其他课程1-2个
INSERT INTO student_courses (student_id, course_id) VALUES
(1, 101), -- 小明选修基础数学
(2, 102), -- 小红选修大学英语
(3, 102), -- 小刚选修大学英语
(4, 102), -- 小丽选修大学英语（热门课程）
(1, 103), -- 小明选修高等物理
(5, 103), -- 小强选修高等物理
(4, 104); -- 小丽选修编程入门
数据关系可视化：
课程 '大学英语'(102)
↑
|--- 被学生『小红』选修
|--- 被学生『小刚』选修
|--- 被学生『小丽』选修
课程 '高等物理'(103)
↑
|--- 被学生『小明』选修
|--- 被学生『小强』选修
3. 问题重现：直接分页为什么不行？
如果我们想按课程创建时间降序排列，每页显示2门课程，第一页的简单查询是：
SELECT c.course_id, c.title, s.name AS student_name
FROM courses c
LEFT JOIN student_courses sc ON c.course_id = sc.course_id
LEFT JOIN students s ON sc.student_id = s.student_id
ORDER BY c.create_time DESC
LIMIT 2 OFFSET 0;
查询结果会出乎意料：
| course_id | title | student_name | |
|---|---|---|---|
| 104 | 编程入门 | 小丽 | |
| 103 | 高等物理 | 小明 | <-- 第一页结束了！？ |
| 103 | 高等物理 | 小强 |
问题分析：
数据库严格按照 LIMIT 2 的要求，只返回了前2行数据。但这2行数据只包含了一门半课程（一门完整的“编程入门”和半门“高等物理”，缺少了学生“小强”）。“大学英语”这门课根本没能出现在第一页。这完全不符合我们“按课程分页”的业务需求。
4. 解决方案：使用窗口函数进行去重分页
现在，我们来详解你提供的优化SQL，它如何一步步解决这个问题。
SQL 代码分解讲解
-- 第一步：使用公共表表达式(CTE)为课程排序编号
WITH unique_courses AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY create_time DESC) as rn
FROM courses
),
-- 第二步：根据上一步的编号进行分页，精准取出第1页的2门课程
paginated_courses AS (
SELECT * FROM unique_courses WHERE rn BETWEEN 1 AND 2
)
-- 第三步：将分页得到的课程与学生关联，获取这些课程的完整信息
SELECT
c.course_id,
c.title,
c.create_time,
s.name AS student_name
FROM paginated_courses c
LEFT JOIN student_courses sc ON c.course_id = sc.course_id
LEFT JOIN students s ON sc.student_id = s.student_id
ORDER BY c.create_time DESC, c.course_id, s.student_id; -- 保持排序正确
第一部分：unique_courses CTE
SELECT *,
ROW_NUMBER() OVER (ORDER BY create_time DESC) as rn
FROM courses
-
ROW_NUMBER() OVER (ORDER BY create_time DESC): 这是一个窗口函数。它不会改变结果集的行数，而是为每一行计算一个序号。 -
ORDER BY create_time DESC: 在窗口函数内，它指定了编号的规则——按课程创建时间从新到旧排序。 -
as rn: 将计算出的序号命名为rn（row number的缩写）。 -
执行结果：这个CTE会生成一个临时结果，为每一门课程分配一个唯一的、连续的序号。
| course_id | title | create_time | rn |
|---|---|---|---|
| 104 | 编程入门 | 2023-01-04 14:00:00 | 1 |
| 103 | 高等物理 | 2023-01-03 09:00:00 | 2 |
| 102 | 大学英语 | 2023-01-02 11:00:00 | 3 |
| 101 | 基础数学 | 2023-01-01 10:00:00 | 4 |
第二部分：paginated_courses CTE
SELECT * FROM unique_courses WHERE rn BETWEEN 1 AND 2
-
这一步非常简单：从
unique_courses中筛选出序号为 1 和 2 的行。 -
这就是分页的核心：我们是在课程主体上做分页，而不是在最终连接后的结果集上分页。我们精准地取出了第1页的2门课程：
编程入门和高等物理。
第三部分：主查询
SELECT ...
FROM paginated_courses c
LEFT JOIN student_courses sc ON c.course_id = sc.course_id
LEFT JOIN students s ON sc.student_id = s.student_id
ORDER BY ...
-
现在，我们用这2门课程 (
c) 去左连接student_courses(sc) 和students(s) 表。 -
因为“编程入门”有1个学生，“高等物理”有2个学生，所以最终连接后会产生 1 + 2 = 3 行数据。
-
最终结果：
| course_id | title | create_time | student_name |
|---|---|---|---|
| 104 | 编程入门 | 2023-01-04 14:00:00 | 小丽 |
| 103 | 高等物理 | 2023-01-03 09:00:00 | 小明 |
| 103 | 高等物理 | 2023-01-03 09:00:00 | 小强 |
成功！ 这就是我们想要的结果：
-
分页正确：第一页确实只包含了2门课程（104和103）。
-
数据完整：每门课程的所有学生信息都完整地展示了出来，没有丢失。
-
性能尚可：虽然“大学英语”有3个学生，但因为它不在这一页，所以不需要进行连接计算，节省了资源。数据库只需要为2门课连接对应的学生。
5. 总结
这种方法的核心智慧在于：将“分页”和“连接”这两个操作解耦。
-
先分页（基于主体）：在主体表（
courses）上利用窗口函数进行排序和分页计算，精准地确定要显示哪些课程。 -
后连接（获取明细）：将分页得到的主体数据（2门课）再去关联其他表，获取这些主体对应的所有明细数据（学生）。
这样就完美规避了在多对多关系中直接使用 LIMIT 导致的数据截断和丢失问题。虽然最终返回的行数可能多于每页设定的数量，但这是业务逻辑的正确体现——以课程为主体进行分页。
posted on
浙公网安备 33010602011771号