学习下mysql新版本支持的row_number方法

发表于 2025-07-27 分类于 mysql 阅读次数：阅读次数： Disqus：

在实际开发中，我们经常遇到需要在分组内进行排序并获取特定排名记录的需求。比如查找每个班级年龄最大的学生，每个部门薪资最高的员工等。这类问题在MySQL 8.0前后有着截然不同的解决方案。

传统解决方案（MySQL 8.0之前）

表结构

CREATE TABLE IF NOT EXISTS `students` (
  `id` bigint(32) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(64) NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年纪',
  `class` int(11) NOT NULL COMMENT '班级',
  `created_at` datetime NOT NULL COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp() COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

业务场景

假设我们有如下学生数据，我想找到每个班年纪最大的学生，可以用一些联表或者子查询方法

id	name	age	class
1	学生1	10	1
2	学生2	10	2
3	学生3	11	1
4	学生3	11	2

子查询联表

在MySQL 8.0之前，我们需要使用相对复杂的子查询配合JOIN来实现：

SELECT ta.* 
FROM students ta 
JOIN (
    SELECT class, MAX(age) as max_age 
    FROM students 
    GROUP BY class
) as tb 
ON ta.class = tb.class AND ta.age = tb.max_age;

分析：

子查询阶段：SELECT class, MAX(age) as max_age FROM students GROUP BY class
- 按班级分组，找出每个班级的最大年龄
- 结果类似：{class: 1, max_age: 20}, {class: 2, max_age: 21}
JOIN阶段：将原表与子查询结果关联
- 关联条件：ta.class = tb.class AND ta.age = tb.max_age
- 最终获得每个班级年龄最大的学生完整信息

局限性：

查询逻辑相对复杂，可读性不佳
如果存在同班级同年龄的多个学生，会返回多条记录
性能上需要进行两次表扫描和一次JOIN操作

现代解决方案（MySQL 8.0+）

使用窗口函数优化

MySQL 8.0引入了窗口函数，让这类问题的解决变得更加优雅：

SELECT * FROM (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY class ORDER BY age DESC) as row_num
    FROM students
) t 
WHERE row_num = 1;

核心概念解析：

PARTITION BY class：按班级进行分区
- 可以理解为逻辑上的分组，但不会像GROUP BY那样聚合数据
- 每个分区内部可以独立进行排序和编号
ORDER BY age DESC：在每个分区内按年龄降序排序
- 年龄最大的学生排在第一位
**ROW_NUMBER()**：为每个分区内的行分配唯一序号
- 从1开始递增，即使有相同值也会分配不同序号
- 这就解决了传统方法中重复值的问题
外层WHERE row_num = 1：筛选每个分区的第一条记录

其他窗口函数选择

除了ROW_NUMBER()，还可以根据业务需求选择其他窗口函数：

-- 使用RANK()：相同值会有相同排名，但会跳跃序号
SELECT * FROM (
    SELECT *, 
           RANK() OVER (PARTITION BY class ORDER BY age DESC) as rank_num
    FROM students
) t 
WHERE rank_num = 1;

-- 使用DENSE_RANK()：相同值有相同排名，但不跳跃序号
SELECT * FROM (
    SELECT *, 
           DENSE_RANK() OVER (PARTITION BY class ORDER BY age DESC) as dense_rank_num
    FROM students
) t 
WHERE dense_rank_num = 1;

性能对比与优势

窗口函数的优势：

代码简洁性：逻辑更直观，维护成本更低
性能优化：只需一次表扫描，避免了JOIN操作
功能丰富：提供多种排名函数应对不同场景
处理重复值：ROW_NUMBER()确保每个分组只返回一条记录

适用场景扩展：

Top-N查询：每个分组的前N条记录
数据去重：基于特定字段的去重逻辑
百分位计算：使用PERCENT_RANK()等函数
移动平均：结合ROWS BETWEEN进行滑动窗口计算

MySQL 8.0的窗口函数为分组排序查询提供了更现代化的解决方案。相比传统的子查询+JOIN方式，窗口函数不仅在语法上更加简洁直观，在性能上也有显著提升。对于需要在分组内进行复杂数据分析的场景，窗口函数已经成为不可或缺的利器。