MySQL 中的 ROW_NUMBER() 窗口函数

在 MySQL 中，ROW_NUMBER() 是一个窗口函数，用于为查询结果中的每一行分配一个连续的序号。它常用于数据排名、分页、去重、分组取 Top N 等场景。

ROW_NUMBER() 从 MySQL 8.0 开始支持。

一、`ROW_NUMBER()` 的基本作用

ROW_NUMBER() 会按照指定的排序规则，为结果集中的每一行生成一个唯一的行号。

基本语法如下：

ROW_NUMBER() OVER (
    [PARTITION BY 分组字段]
    ORDER BY 排序字段
)

其中：

ROW_NUMBER()：生成行号
OVER()：表示窗口函数的作用范围
PARTITION BY：可选，用于分组编号
ORDER BY：必选，用于指定编号顺序

二、准备示例数据

假设有一张学生成绩表 student_score：

CREATE TABLE student_score (
    id INT PRIMARY KEY,
    student_name VARCHAR(50),
    subject VARCHAR(50),
    score INT
);

插入测试数据：

INSERT INTO student_score (id, student_name, subject, score) VALUES
(1, 'Alice', 'Math', 95),
(2, 'Bob', 'Math', 88),
(3, 'Cindy', 'Math', 95),
(4, 'David', 'English', 90),
(5, 'Eva', 'English', 85),
(6, 'Frank', 'English', 92);

三、基本用法：给所有数据编号

SELECT
    student_name,
    subject,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM student_score;

查询结果示例：

student_name	subject	score	row_num
Alice	Math	95	1
Cindy	Math	95	2
Frank	English	92	3
David	English	90	4
Bob	Math	88	5
Eva	English	85	6

这里按照 score DESC 对所有学生成绩进行降序排序，并生成连续行号。

需要注意的是，如果排序字段存在相同值，例如 Alice 和 Cindy 的分数都是 95，那么它们之间的先后顺序可能不稳定。为了保证结果稳定，建议增加一个唯一字段作为辅助排序条件：

SELECT
    student_name,
    subject,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC, id ASC) AS row_num
FROM student_score;

四、分组编号：`PARTITION BY`

如果希望每个科目内部单独排名，可以使用 PARTITION BY。

SELECT
    student_name,
    subject,
    score,
    ROW_NUMBER() OVER (
        PARTITION BY subject
        ORDER BY score DESC
    ) AS row_num
FROM student_score;

结果示例：

student_name	subject	score	row_num
Frank	English	92	1
David	English	90	2
Eva	English	85	3
Alice	Math	95	1
Cindy	Math	95	2
Bob	Math	88	3

这里 PARTITION BY subject 表示按照科目分组，每个科目内部都会从 1 开始编号。

五、常见应用场景

1. 分组取第一条数据

例如，查询每个科目成绩最高的学生：

SELECT *
FROM (
    SELECT
        student_name,
        subject,
        score,
        ROW_NUMBER() OVER (
            PARTITION BY subject
            ORDER BY score DESC, id ASC
        ) AS row_num
    FROM student_score
) t
WHERE t.row_num = 1;

结果示例：

student_name	subject	score	row_num
Frank	English	92	1
Alice	Math	95	1

这个写法在实际开发中非常常见，常用于“每组取最新一条记录”“每个用户取最近一次登录记录”等场景。

2. 分组取 Top N

例如，查询每个科目前 2 名学生：

SELECT *
FROM (
    SELECT
        student_name,
        subject,
        score,
        ROW_NUMBER() OVER (
            PARTITION BY subject
            ORDER BY score DESC, id ASC
        ) AS row_num
    FROM student_score
) t
WHERE t.row_num <= 2;

这种写法适合处理“每个分类下取销量最高的前 N 个商品”“每个部门取工资最高的前 N 名员工”等需求。

3. 删除重复数据

假设有一张用户表 user_info，其中 email 可能重复：

CREATE TABLE user_info (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    created_at DATETIME
);

如果希望按照 email 去重，只保留每个邮箱最早创建的一条记录，可以先找出重复数据：

SELECT *
FROM (
    SELECT
        id,
        name,
        email,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY created_at ASC, id ASC
        ) AS row_num
    FROM user_info
) t
WHERE t.row_num > 1;

如果确认要删除重复记录，可以这样写：

DELETE FROM user_info
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (
                PARTITION BY email
                ORDER BY created_at ASC, id ASC
            ) AS row_num
        FROM user_info
    ) t
    WHERE t.row_num > 1
);

这里需要多套一层子查询，是为了避免 MySQL 在删除时直接引用同一张表导致错误。

4. 实现分页查询

ROW_NUMBER() 也可以用于分页。

例如，查询第 11 到第 20 条数据：

SELECT *
FROM (
    SELECT
        id,
        student_name,
        subject,
        score,
        ROW_NUMBER() OVER (ORDER BY id ASC) AS row_num
    FROM student_score
) t
WHERE t.row_num BETWEEN 11 AND 20;

不过在 MySQL 中，普通分页通常更常用 LIMIT：

SELECT *
FROM student_score
ORDER BY id ASC
LIMIT 10 OFFSET 10;

ROW_NUMBER() 更适合复杂分页，例如分页前还需要分组、排序或计算排名。

六、`ROW_NUMBER()`、`RANK()` 和 `DENSE_RANK()` 的区别

MySQL 中常见的排名窗口函数有三个：

函数	相同排序值是否同名次	名次是否跳跃
`ROW_NUMBER()`	否	否
`RANK()`	是	是
`DENSE_RANK()`	是	否

假设成绩如下：

name	score
Alice	95
Cindy	95
Frank	90

执行：

SELECT
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK() OVER (ORDER BY score DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num
FROM student_score;

结果可能是：

name	score	row_num	rank_num	dense_rank_num
Alice	95	1	1	1
Cindy	95	2	1	1
Frank	90	3	3	2

区别如下：

ROW_NUMBER()：即使分数相同，也会分配不同序号
RANK()：相同分数排名相同，但后续排名会跳跃
DENSE_RANK()：相同分数排名相同，后续排名不会跳跃

七、使用注意事项

1. `ORDER BY` 很重要

ROW_NUMBER() 必须依赖排序规则生成行号。如果排序字段不唯一，结果顺序可能不稳定。

推荐写法：

ROW_NUMBER() OVER (ORDER BY score DESC, id ASC)

不要只写：

ROW_NUMBER() OVER (ORDER BY score DESC)

如果 score 有重复值，编号结果可能不稳定。

2. `ROW_NUMBER()` 不能直接写在 `WHERE` 中

错误写法：

SELECT
    student_name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM student_score
WHERE row_num = 1;

原因是 SQL 的执行顺序中，WHERE 先于 SELECT 执行，此时 row_num 还没有生成。

正确写法是使用子查询：

SELECT *
FROM (
    SELECT
        student_name,
        score,
        ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
    FROM student_score
) t
WHERE t.row_num = 1;

3. 注意 MySQL 版本

ROW_NUMBER() 是 MySQL 8.0 引入的窗口函数。如果使用的是 MySQL 5.7 或更低版本，不能直接使用该函数。

在旧版本中，通常只能使用用户变量模拟行号，但这种写法可读性和稳定性都不如窗口函数。

八、总结

ROW_NUMBER() 是 MySQL 中非常实用的窗口函数，主要用于为查询结果生成连续编号。

它的典型使用场景包括：

查询结果编号
分组排名
每组取第一条数据
每组取 Top N
删除重复数据
复杂分页查询

核心语法如下：

ROW_NUMBER() OVER (
    PARTITION BY 分组字段
    ORDER BY 排序字段
)

在实际开发中，使用 ROW_NUMBER() 时应特别注意排序字段的稳定性。如果排序字段可能重复，建议增加主键或唯一字段作为辅助排序条件，从而保证查询结果可预测、可复现。

一、ROW_NUMBER() 的基本作用