SQL中row_number怎么用行号生成的分区排序技巧-SQL-PHP中文网

SQL中row_number怎么用行号生成的分区排序技巧

裘德小鎮的故事

发布： 2025-06-23 13:26:01

原创

196人浏览过

row_number()函数用于为sql结果集的行分配唯一连续整数，常与over()结合使用。其基本语法包括可选的partition by（按列分组）和必填的order by（指定排序），生成的序号从1开始递增。常见应用场景有：1. 生成全局或分组内行号；2. 获取每个分组的前n条记录；3. 分页查询；4. 数据清洗去重；5. 报表添加序号列；6. 数据趋势分析。注意点包括：无order by时结果不可控；不处理并列排名，需用rank()或dense_rank()替代；性能优化可通过索引、减少分区大小等方式实现。

SQL中row_number怎么用行号生成的分区排序技巧

SQL中ROW_NUMBER()函数用于为结果集中的每一行分配一个唯一的连续整数。它通常与OVER()子句结合使用，以指定行号的排序和分区方式。简单来说，它能让你给查询结果加上一列，这列数字从1开始，依次递增，就像给表格加了个自动编号的功能。

解决方案

ROW_NUMBER()的基本语法如下：

ROW_NUMBER() OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column3 [ASC | DESC], column4 [ASC | DESC], ...
) AS row_num

登录后复制

PARTITION BY子句（可选）：将结果集分成多个分区。ROW_NUMBER()函数会在每个分区内独立地分配行号。如果没有PARTITION BY子句，则整个结果集被视为一个分区。
ORDER BY子句：定义每个分区内行号的排序方式。
AS row_num：为生成的行号列指定一个别名。

示例：简单行号生成

假设我们有一个employees表，包含id, name, department, salary等字段。要为所有员工分配一个行号，可以这样写：

SELECT
    id,
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM
    employees;

登录后复制

这个查询会按照salary降序排列所有员工，并为每个人分配一个行号，row_num列的值从1开始，工资最高的员工的row_num为1。

示例：分区排序

如果想按部门对员工进行排序，并在每个部门内分配行号，可以使用PARTITION BY子句：

SELECT
    id,
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM
    employees;

登录后复制

这个查询会将员工按department分组，然后在每个部门内按照salary降序排列，并为每个员工分配一个行号。每个部门工资最高的员工的row_num为1。

应用场景：获取每个分组的前N条记录

ROW_NUMBER()一个常见的应用场景是获取每个分组的前N条记录。例如，要获取每个部门工资最高的两名员工，可以这样写：

WITH RankedEmployees AS (
    SELECT
        id,
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
    FROM
        employees
)
SELECT
    id,
    name,
    department,
    salary
FROM
    RankedEmployees
WHERE
    row_num <= 2;

登录后复制

这个查询首先使用WITH子句创建一个名为RankedEmployees的公共表表达式（CTE），在CTE中，我们使用ROW_NUMBER()函数为每个部门的员工分配行号。然后，我们从CTE中选择row_num小于等于2的员工，即每个部门工资最高的两名员工。

一些需要注意的点

ROW_NUMBER()在没有ORDER BY子句的情况下，结果的顺序是不确定的。虽然可以运行，但结果可能每次都不一样，这在很多情况下是不可接受的。
ROW_NUMBER()分配的是连续的整数，即使ORDER BY子句中的值相同，也会分配不同的行号。如果需要处理相同值的情况，可以考虑使用RANK()或DENSE_RANK()函数。

如何处理ROW_NUMBER()结果中的并列排名？

ROW_NUMBER()本身并不处理并列排名，它总是分配唯一的行号。如果需要处理并列排名，可以考虑使用RANK()、DENSE_RANK()或NTILE()函数。

RANK()：为并列的行分配相同的排名，并跳过后续的排名。例如，如果有两个并列第一，则下一个排名是3。
DENSE_RANK()：为并列的行分配相同的排名，但不跳过后续的排名。例如，如果有两个并列第一，则下一个排名是2。
NTILE(N)：将结果集分成N个桶，并为每个桶分配一个桶号。

例如，使用RANK()函数：

SELECT
    id,
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num
FROM
    employees;

登录后复制

如果同一部门有两个员工的工资相同且都是最高，那么他们的rank_num都将是1，而下一个员工的rank_num将是3。

ROW_NUMBER()在性能优化方面有哪些考量？

ROW_NUMBER()函数在处理大数据集时可能会影响性能。以下是一些性能优化方面的考量：

索引： 确保ORDER BY子句中使用的列有索引。这可以显著提高排序的性能。对于PARTITION BY子句中使用的列，也建议创建索引。
避免不必要的排序： 尽量避免在不需要排序的情况下使用ROW_NUMBER()。如果只需要分配唯一的行号，而不需要特定的顺序，可以考虑使用其他方法，例如使用序列生成器。
分区大小： 如果使用PARTITION BY子句，分区的大小会影响性能。较大的分区可能需要更多的内存和处理时间。
查询优化器： 不同的数据库系统可能有不同的查询优化器。了解数据库系统的查询优化器如何处理ROW_NUMBER()函数，可以帮助你编写更高效的查询。

例如，在MySQL 8.0及更高版本中，可以使用窗口函数优化器来提高ROW_NUMBER()函数的性能。

除了分组Top N，ROW_NUMBER()还有哪些实际应用场景？

除了获取分组Top N，ROW_NUMBER()还有很多其他的实际应用场景：

分页： 可以使用ROW_NUMBER()函数来实现分页功能。例如，要获取第10到第20条记录，可以这样写：

WITH PagedResults AS (
    SELECT
        id,
        name,
        department,
        salary,
        ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM
        employees
)
SELECT
    id,
    name,
    department,
    salary
FROM
    PagedResults
WHERE
    row_num BETWEEN 10 AND 20;

登录后复制

数据清洗： 可以使用ROW_NUMBER()函数来识别和删除重复的记录。例如，如果employees表中有重复的记录，可以使用以下查询来删除重复的记录：

WITH DuplicateRows AS (
    SELECT
        id,
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY name, department, salary ORDER BY id) AS row_num
    FROM
        employees
)
DELETE FROM employees
WHERE id IN (SELECT id FROM DuplicateRows WHERE row_num > 1);

登录后复制