MySQL窗口函数的应用

目录

场景1：每个班级成绩前三的学生

这是一个经典的"分组取 Top N"问题，能很好地展示 WHERE、HAVING、窗口函数、以及 WITH 的综合运用。

准备数据

-- 学生表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    class_id INT
);

-- 成绩表
CREATE TABLE scores (
    student_id INT,
    subject VARCHAR(20),
    score INT
);

-- 班级表
CREATE TABLE classes (
    id INT PRIMARY KEY,
    class_name VARCHAR(50)
);

-- 插入数据
INSERT INTO classes VALUES 
(1, '火箭班'),
(2, '重点班'),
(3, '普通班');

INSERT INTO students VALUES 
(1, '张三', 1), (2, '李四', 1), (3, '王五', 1), (4, '赵六', 1), (5, '小明', 1),
(6, '小红', 2), (7, '小刚', 2), (8, '小丽', 2), (9, '小强', 2),
(10, '小华', 3), (11, '小美', 3), (12, '小军', 3), (13, '小芳', 3);

INSERT INTO scores VALUES 
-- 火箭班（班级1）数学成绩
(1, '数学', 98), (2, '数学', 95), (3, '数学', 92), (4, '数学', 92), (5, '数学', 88),
-- 重点班（班级2）数学成绩
(6, '数学', 89), (7, '数学', 85), (8, '数学', 84), (9, '数学', 82),
-- 普通班（班级3）数学成绩
(10, '数学', 75), (11, '数学', 72), (12, '数学', 68), (13, '数学', 65);

查询语句

WITH ranked_students AS (
    SELECT 
        c.class_name,
        s.name,
        sc.score,
        DENSE_RANK() OVER (PARTITION BY s.class_id ORDER BY sc.score DESC) AS rank_num
    FROM students s
    JOIN classes c ON s.class_id = c.id
    JOIN scores sc ON s.id = sc.student_id
    WHERE sc.subject = '数学'
)
SELECT class_name, name, score, rank_num
FROM ranked_students
WHERE rank_num <= 3
ORDER BY class_name, rank_num;

结果：

class_name name score rank_num
火箭班 张三 98 1
火箭班 李四 95 2
火箭班 王五 92 3
火箭班 赵六 92 3
重点班 小红 89 1
重点班 小刚 85 2
重点班 小丽 84 3
普通班 小华 75 1
普通班 小美 72 2
普通班 小军 68 3

说明：

  • DENSE_RANK()：相同分数排名相同，不跳号

  • PARTITION BY s.class_id：每个班级独立排名

  • 外层 WHERE rank_num <= 3 过滤

解析

整体结构

WITH ranked_students AS ( ... )  -- 定义一个临时结果集
SELECT ... FROM ranked_students  -- 从这个临时结果集中查询
WHERE ... ORDER BY ...;

这就像一个管道：先创建一个名为 ranked_students 的临时视图，然后再从中筛选数据。

内部查询（CTE 部分）

FROM 和 JOIN 部分

FROM students s
JOIN classes c ON s.class_id = c.id
JOIN scores sc ON s.id = sc.student_id

作用：将三张表连接起来

执行过程（想象成一个临时大表）：

students classes scores
id=1, name=张三, class_id=1 id=1, class_name=火箭班 student_id=1, subject=数学, score=98
id=2, name=李四, class_id=1 id=1, class_name=火箭班 student_id=2, subject=数学, score=95
id=3, name=王五, class_id=1 id=1, class_name=火箭班 student_id=3, subject=数学, score=92
... ... ...

JOIN 后的中间结果（部分数据）：

s.id s.name s.class_id c.class_name sc.subject sc.score
1 张三 1 火箭班 数学 98
2 李四 1 火箭班 数学 95
3 王五 1 火箭班 数学 92
4 赵六 1 火箭班 数学 92
5 小明 1 火箭班 数学 88
6 小红 2 重点班 数学 89
7 小刚 2 重点班 数学 85
... ... ... ... ... ...

WHERE 过滤

WHERE sc.subject = '数学'

作用：只保留数学成绩的记录

过滤后的结果：

s.id s.name s.class_id c.class_name sc.score
1 张三 1 火箭班 98
2 李四 1 火箭班 95
3 王五 1 火箭班 92
4 赵六 1 火箭班 92
5 小明 1 火箭班 88
6 小红 2 重点班 89
7 小刚 2 重点班 85
8 小丽 2 重点班 84
9 小强 2 重点班 82
10 小华 3 普通班 75
11 小美 3 普通班 72
12 小军 3 普通班 68
13 小芳 3 普通班 65

窗口函数（核心）

DENSE_RANK() OVER (PARTITION BY s.class_id ORDER BY sc.score DESC) AS rank_num

这是整个查询最核心的部分。我们拆解一下：

PARTITION BY s.class_id（分区）

  • 作用：将数据按班级分成独立的组

  • 类比：就像在 Excel 中按"班级"列筛选，分别处理每个班级

分区后的样子：

分区1：火箭班 (class_id=1) 分区2：重点班 (class_id=2) 分区3：普通班 (class_id=3)
张三, 98 小红, 89 小华, 75
李四, 95 小刚, 85 小美, 72
王五, 92 小丽, 84 小军, 68
赵六, 92 小强, 82 小芳, 65
小明, 88
ORDER BY sc.score DESC（排序）
  • 作用：在每个分区内按分数降序排列

排序后：

火箭班 重点班 普通班
98 (张三) 89 (小红) 75 (小华)
95 (李四) 85 (小刚) 72 (小美)
92 (王五) 84 (小丽) 68 (小军)
92 (赵六) 82 (小强) 65 (小芳)
88 (小明)
DENSE_RANK()（密集排名）

  • 作用：给每个分区内的行分配排名

  • 特点：相同分数得到相同排名，且排名连续（不跳号）

排名过程：

火箭班 分数 排名过程 最终排名
张三 98 第1个，排名1 1
李四 95 第2个，排名2 2
王五 92 第3个，与赵六并列，排名3 3
赵六 92 与王五并列，也是排名3 3
小明 88 第5个，但排名是4 4

对比不同排名函数：

  • ROW_NUMBER(): 1,2,3,4,5（不并列，唯一编号）
  • RANK(): 1,2,3,3,5（并列后跳号）
  • DENSE_RANK(): 1,2,3,3,4（并列后不跳号）✅
SELECT 选择的列
SELECT
    c.class_name,    -- 班级名称（火箭班/重点班/普通班）
    s.name,          -- 学生姓名
    sc.score,        -- 数学成绩
    rank_num         -- 刚才计算的排名

CTE 执行后的 ranked_students 结果：

class_name name score rank_num
火箭班 张三 98 1
火箭班 李四 95 2
火箭班 王五 92 3
火箭班 赵六 92 3
火箭班 小明 88 4
重点班 小红 89 1
重点班 小刚 85 2
重点班 小丽 84 3
重点班 小强 82 4
普通班 小华 75 1
普通班 小美 72 2
普通班 小军 68 3
普通班 小芳 65 4

外部查询

SELECT class_name, name, score, rank_num
FROM ranked_students
WHERE rank_num <= 3
ORDER BY class_name, rank_num;

WHERE rank_num <= 3

  • 作用：过滤掉排名大于3的学生

  • 注意：这里的 WHERE 是在 CTE 的结果上过滤，不是过滤原始数据

过滤后：

class_name name score rank_num
火箭班 张三 98 1
火箭班 李四 95 2
火箭班 王五 92 3
火箭班 赵六 92 3
重点班 小红 89 1
重点班 小刚 85 2
重点班 小丽 84 3
普通班 小华 75 1
普通班 小美 72 2
普通班 小军 68 3

ORDER BY class_name, rank_num

  • 作用：先按班级排序，再按排名排序

最终结果：

class_name name score rank_num
普通班 小华 75 1
普通班 小美 72 2
普通班 小军 68 3
重点班 小红 89 1
重点班 小刚 85 2
重点班 小丽 84 3
火箭班 张三 98 1
火箭班 李四 95 2
火箭班 王五 92 3
火箭班 赵六 92 3

完整执行流程图

原始数据
    ↓
JOIN 三张表
    ↓
WHERE subject = '数学'  ← 过滤科目
    ↓
按 class_id 分区 (PARTITION BY)
    ↓
每个分区内按 score 降序排序
    ↓
计算 DENSE_RANK()  ← 添加排名列
    ↓
创建临时表 ranked_students
    ↓
WHERE rank_num <= 3  ← 过滤排名
    ↓
ORDER BY class_name, rank_num  ← 排序
    ↓
最终结果

场景2：多科目 + 总分排名

-- 先计算每个学生各科总分
WITH student_total_scores AS (
    SELECT 
        s.id,
        s.name,
        c.class_name,
        s.class_id,
        SUM(sc.score) AS total_score,
        COUNT(sc.subject) AS subject_count
    FROM students s
    JOIN classes c ON s.class_id = c.id
    JOIN scores sc ON s.id = sc.student_id
    GROUP BY s.id, s.name, c.class_name, s.class_id
    HAVING subject_count = (SELECT COUNT(DISTINCT subject) FROM scores)  -- 确保考了所有科目
),
ranked_by_class AS (
    SELECT 
        class_name,
        name,
        total_score,
        ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY total_score DESC) AS rank_num
    FROM student_total_scores
)
SELECT class_name, name, total_score, rank_num
FROM ranked_by_class
WHERE rank_num <= 3
ORDER BY class_name, rank_num;

这是一个多步骤的复杂查询，用于找出每个班级总分排名前三（且考了所有科目）的学生。让我们从外到内逐步拆解。

整体结构

WITH student_total_scores AS ( ... ),    -- CTE1: 计算每个学生总分
     ranked_by_class AS ( ... )          -- CTE2: 班级内排名
SELECT ... FROM ranked_by_class          -- 主查询: 取前三名
WHERE ... ORDER BY ...;

这是一个CTE链：第二个CTE依赖第一个CTE的结果，主查询依赖第二个CTE的结果。

第一部分：student_total_scores CTE

完整代码

WITH student_total_scores AS (
    SELECT 
        s.id,
        s.name,
        c.class_name,
        s.class_id,
        SUM(sc.score) AS total_score,
        COUNT(sc.subject) AS subject_count
    FROM students s
    JOIN classes c ON s.class_id = c.id
    JOIN scores sc ON s.id = sc.student_id
    GROUP BY s.id, s.name, c.class_name, s.class_id
    HAVING subject_count = (SELECT COUNT(DISTINCT subject) FROM scores)
)

逐步拆解

步骤1：JOIN 三张表

FROM students s
JOIN classes c ON s.class_id = c.id
JOIN scores sc ON s.id = sc.student_id

生成中间数据（假设原始数据）：

s.id s.name s.class_id c.class_name sc.subject sc.score
1 张三 1 火箭班 数学 98
1 张三 1 火箭班 语文 90
1 张三 1 火箭班 英语 92
2 李四 1 火箭班 数学 95
2 李四 1 火箭班 语文 88
2 李四 1 火箭班 英语 91
6 小红 2 重点班 数学 89
6 小红 2 重点班 语文 85
... ... ... ... ... ...

步骤2：GROUP BY 聚合

GROUP BY s.id, s.name, c.class_name, s.class_id

按学生分组，计算每个学生的：

  • SUM(sc.score)：总分
  • COUNT(sc.subject)：考试科目数

聚合后的结果：

id name class_name class_id total_score subject_count
1 张三 火箭班 1 280 3
2 李四 火箭班 1 274 3
3 王五 火箭班 1 270 3
4 赵六 火箭班 1 270 3
5 小明 火箭班 1 260 3
6 小红 重点班 2 260 3
7 小刚 重点班 2 255 3
... ... ... ... ... ...

步骤3：HAVING 过滤

HAVING subject_count = (SELECT COUNT(DISTINCT subject) FROM scores)

子查询 (SELECT COUNT(DISTINCT subject) FROM scores)：

  • 假设 scores 表中有：数学、语文、英语
  • COUNT(DISTINCT subject) = 3
  • 意思是：全校总共开设了 3 门科目

HAVING 的作用：

  • 只保留 subject_count = 3 的学生
  • 即考了所有科目的学生（没有缺考）

为什么要用 HAVING 而不是 WHERE？

  • subject_count 是聚合函数的结果
  • WHERE 在聚合前执行，此时还没有 subject_count
  • HAVING 在聚合后执行，可以过滤聚合结果

过滤后的结果（假设有学生缺考）：

id name class_name class_id total_score subject_count
1 张三 火箭班 1 280 3
2 李四 火箭班 1 274 3
3 王五 火箭班 1 270 3
4 赵六 火箭班 1 270 3
5 小明 火箭班 1 260 3
6 小红 重点班 2 260 3
7 小刚 重点班 2 255 3
8 小丽 重点班 2 254 3
9 小强 重点班 2 248 3
10 小华 普通班 3 225 3
11 小美 普通班 3 216 3
12 小军 普通班 3 204 3
13 小芳 普通班 3 195 3

注意：如果有学生只考了2门，就会被过滤掉。

第二部分：ranked_by_class CTE

完整代码

ranked_by_class AS (
    SELECT 
        class_name,
        name,
        total_score,
        ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY total_score DESC) AS rank_num
    FROM student_total_scores
)

逐步拆解

步骤1：从 student_total_scores 中选择

只取需要的列：班级名、学生名、总分

步骤2：窗口函数排名

ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY total_score DESC)

分解说明：

  • PARTITION BY class_id：按班级分组（每个班级独立排名）
  • ORDER BY total_score DESC：按总分降序排列（分高的排前面）
  • ROW_NUMBER()：给每个班级内的学生编号（1,2,3,4...）

执行过程可视化：

火箭班（class_id=1）：

name total_score 排序后位置 ROW_NUMBER
张三 280 第1 1
李四 274 第2 2
王五 270 第3 3
赵六 270 第4 4
小明 260 第5 5

重点班（class_id=2）：

name total_score 排序后位置 ROW_NUMBER
小红 260 第1 1
小刚 255 第2 2
小丽 254 第3 3
小强 248 第4 4

普通班（class_id=3）：

name total_score 排序后位置 ROW_NUMBER
小华 225 第1 1
小美 216 第2 2
小军 204 第3 3
小芳 195 第4 4

ranked_by_class 的结果：

class_name name total_score rank_num
火箭班 张三 280 1
火箭班 李四 274 2
火箭班 王五 270 3
火箭班 赵六 270 4
火箭班 小明 260 5
重点班 小红 260 1
重点班 小刚 255 2
重点班 小丽 254 3
重点班 小强 248 4
普通班 小华 225 1
普通班 小美 216 2
普通班 小军 204 3
普通班 小芳 195 4

第三部分：主查询

完整代码

SELECT class_name, name, total_score, rank_num
FROM ranked_by_class
WHERE rank_num <= 3
ORDER BY class_name, rank_num;

执行过程

步骤1：过滤排名

WHERE rank_num <= 3

只保留每个班级的前3名：

class_name name total_score rank_num
火箭班 张三 280 1
火箭班 李四 274 2
火箭班 王五 270 3
重点班 小红 260 1
重点班 小刚 255 2
重点班 小丽 254 3
普通班 小华 225 1
普通班 小美 216 2
普通班 小军 204 3

注意：火箭班的赵六（第4名）和小明（第5名）被过滤掉了。

步骤2：排序

ORDER BY class_name, rank_num

先按班级名排序，再按排名排序：

最终结果：

class_name name total_score rank_num
普通班 小华 225 1
普通班 小美 216 2
普通班 小军 204 3
重点班 小红 260 1
重点班 小刚 255 2
重点班 小丽 254 3
火箭班 张三 280 1
火箭班 李四 274 2
火箭班 王五 270 3

完整执行流程图

原始数据
    ↓
JOIN 三张表
    ↓
GROUP BY 学生ID
    ↓
计算 total_score 和 subject_count
    ↓
HAVING subject_count = 总科目数  ← 过滤缺考学生
    ↓
student_total_scores (CTE1)
    ↓
窗口函数 ROW_NUMBER() 
PARTITION BY class_id 
ORDER BY total_score DESC
    ↓
ranked_by_class (CTE2)
    ↓
WHERE rank_num <= 3  ← 取前三名
    ↓
ORDER BY class_name, rank_num
    ↓
最终结果

面试常见追问

Q：如果要求"每个班级前三名，且分数不能低于80分"怎么办？

WITH ranked_students AS (...)
SELECT class_name, name, score
FROM ranked_students
WHERE rank_num <= 3 AND score >= 80;  -- WHERE 同时过滤

Q：如果并列第四名也要显示（取前3个分数段）？

使用 DENSE_RANK() 替代 ROW_NUMBER()

Q：如果要求每个班级前三名，且要显示排名序号？

窗口函数天然支持排名列

结论：WITH + 窗口函数是处理"分组 Top N"问题的标准解法，WHERE 和 HAVING 各有分工。

posted @ 2026-04-14 11:48  LARRY1024  阅读(5)  评论(0)    收藏  举报