ROW_NUMBER() OVER实战：优雅实现分组取最新记录，一行代码搞定学生最新成绩查询。

作为一名班主任，你是不是经常需要查看每个学生最近一次考试的成绩？今天要介绍的SQL窗口函数，就能帮你轻松解决这个问题！

真实的教学场景

假设我们是一所学校的教务系统管理员，需要为每个班级的每个学生获取他们最近一次考试的成绩。

我们有三个主要数据表：

1. 学生表：记录学生基本信息
2. 考试成绩表：记录每次考试的成绩
3. 班级信息表：记录班级状态

数据表结构（简化）

-- 学生表
CREATE TABLE students (
    id INT PRIMARY KEY,          -- 学生ID
    student_no VARCHAR(20),      -- 学号
    class_id INT,               -- 班级ID
    student_name VARCHAR(50)    -- 学生姓名
);

-- 考试成绩表  
CREATE TABLE exam_scores (
    id INT PRIMARY KEY,          -- 记录ID
    student_id INT,             -- 学生ID
    exam_date DATE,             -- 考试日期
    subject VARCHAR(50),        -- 科目
    score INT,                  -- 分数
    update_time DATETIME        -- 更新时间
);

-- 班级表
CREATE TABLE classes (
    id INT PRIMARY KEY,         -- 班级ID
    class_name VARCHAR(50),     -- 班级名称
    is_active TINYINT(1)        -- 是否活跃
);

解决方案：窗口函数

-- 获取每个学生最近一次考试的成绩
SELECT student_id, student_no, class_name, subject, score, exam_date
FROM (
    SELECT 
        s.id AS student_id,
        s.student_no,
        s.student_name,
        c.class_name,
        es.subject,
        es.score,
        es.exam_date,
        es.update_time,
        -- 核心魔法在这里！
        ROW_NUMBER() OVER (
            PARTITION BY s.id  -- 按学生ID分组
            ORDER BY es.exam_date DESC  -- 按考试日期降序排列
        ) AS row_num
    FROM students s
    INNER JOIN exam_scores es ON s.id = es.student_id
    LEFT JOIN classes c ON s.class_id = c.id AND c.is_active = 1
    WHERE s.student_no IN ('2023001', '2023002', '2023003')
) temp_table
WHERE row_num = 1  -- 只取每个学生的第一条（最新的）记录
ORDER BY class_name, student_no;

逐步拆解这个"魔法"

第一步：理解数据

假设学生张三的考试记录：

ID: 2023001, 张三的考试记录：
1. 2023-09-01 数学 85分
2. 2023-10-08 数学 90分 ← 最新的
3. 2023-08-20 数学 78分

第二步：窗口函数执行过程

ROW_NUMBER() OVER (
    PARTITION BY s.id          -- 对每个学生单独编号
    ORDER BY es.exam_date DESC -- 按考试日期从新到旧排序
) AS row_num

执行结果会是：

学生2023001（张三）：
考试日期     科目   分数   row_num
2023-10-08   数学    90     1 ← 最新的一次
2023-09-01   数学    85     2
2023-08-20   数学    78     3

学生2023002（李四）：
考试日期     科目   分数   row_num  
2023-10-05   英语    92     1 ← 最新的一次
2023-09-02   英语    88     2

第三步：筛选最新记录

WHERE row_num = 1

这样就只保留了每个学生最近的那次考试成绩。

实际应用场景

场景1：成绩单打印

-- 打印高一一班所有学生最近一次数学考试成绩
WHERE c.class_name = '高一一班' 
  AND es.subject = '数学'
  AND row_num = 1

场景2：进步奖评选

-- 找出每个学生最近两次考试，计算进步情况
SELECT * FROM (
    SELECT ...,
        ROW_NUMBER() OVER (
            PARTITION BY student_id 
            ORDER BY exam_date DESC
        ) AS row_num
    ...
) WHERE row_num <= 2  -- 取最近两次考试

传统方法的对比

传统方法（子查询）：

-- 复杂且效率低
SELECT s.*, es1.*
FROM students s
INNER JOIN exam_scores es1 ON s.id = es1.student_id
INNER JOIN (
    SELECT student_id, MAX(exam_date) as latest_date
    FROM exam_scores
    GROUP BY student_id
) es2 ON es1.student_id = es2.student_id 
       AND es1.exam_date = es2.latest_date

窗口函数方法：

• 代码简洁：逻辑一目了然
• 性能更优：通常执行效率更高
• 扩展性强：轻松调整取第N条记录

窗口函数其他妙用

1. 排名功能

-- 每个班级内按成绩排名
RANK() OVER (
    PARTITION BY class_id 
    ORDER BY score DESC
) AS class_rank

2. 计算平均值

-- 计算每个学生与班级平均分的差距
AVG(score) OVER (
    PARTITION BY class_id
) AS class_avg_score

3. 累计计算

-- 计算每个学生成绩的累计和
SUM(score) OVER (
    PARTITION BY student_id 
    ORDER BY exam_date
) AS cumulative_score

最佳实践建议

1. 索引优化：确保exam_date字段有索引
2. 分区字段：选择合适的分区字段，避免数据倾斜
3. 排序字段：使用有索引的字段排序提升性能
4. 结果验证：先用小数据量测试，确保逻辑正确

总结

通过ROW_NUMBER() OVER窗口函数，我们可以轻松解决"每组取最新/最老记录"这类常见需求。就像老师快速找出每个学生最新成绩一样简单！

关键记住三点：

1. PARTITION BY：告诉SQL如何分组
2. ORDER BY DESC：告诉SQL如何排序（DESC取最新）
3. WHERE row_num = 1：告诉SQL只要每组第一条