sql 中 row_number 用法_sql 中 row_number 行号生成指南

林译川

2026-09-15 • 👁 0人浏览过

row_number() 是 sql 中用于为结果集的每一行分配唯一递增序号的窗口函数。其基本语法为 row_number() over (partition by column_list order by column_list)，其中 partition by 用于定义分组，order by 定义排序规则。它在排名、分页和去重场景中非常实用。与 rank() 和 dense_rank() 不同，row_number() 保证每行都有唯一编号，即使存在并列值也不会重复。1. 数据去重：通过定义重复逻辑（partition by）和保留标准（order by），可筛选出每组中指定序号的记录；2. 分页查询：通过生成行号并筛选特定范围的数据实现高效分页。使用时需注意性能优化、结果确定性、内存消耗及调试问题。建议为排序字段添加索引、确保排序唯一性、减少处理数据量，并通过中间结果进行调试。

ROW_NUMBER()

在 SQL 中是一个窗口函数，它的核心作用是为结果集中的每一行分配一个唯一的、递增的序号。这个序号的生成是基于你定义的“窗口”——也就是一个数据分组（

PARTITION BY

）和一个排序规则（

ORDER BY

）。简单来说，它让你能在每个分组内，按照某个顺序给行打上标签，从1开始。这在需要对数据进行排名、分页或者去重时，都显得格外实用。解决方案理解

ROW_NUMBER()

的关键在于

OVER()

子句。这个子句定义了窗口函数的行为范围。基本语法是这样的：

ROW_NUMBER() OVER (PARTITION BY column1, column2... ORDER BY columnA [ASC|DESC], columnB [ASC|DESC]...)

PARTITION BY

: 这一部分是可选的。如果你指定了它，

ROW_NUMBER()

会将你的数据集分成若干个独立的组（分区），然后每个组内部都会从1开始重新编号。比如，如果你想在每个班级内部给学生排名，那么

PARTITION BY 班级ID

就非常合适。

ORDER BY

: 这一部分是强制的。它定义了在每个分区（或者如果没有

PARTITION BY

，就是整个结果集）内部，行号是按照什么顺序分配的。比如，按分数从高到低排序，那么分数最高的会得到1号。举个例子，假设我们有一个销售订单表

Orders

，包含

CustomerID

OrderDate

OrderAmount

。我们想找出每个客户的最新一笔订单。

SELECT
    CustomerID,
    OrderDate,
    OrderAmount,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC, OrderAmount DESC) AS rn
FROM
    Orders;

在这个例子中，

PARTITION BY CustomerID

确保了我们是针对每个客户独立编号。

ORDER BY OrderDate DESC, OrderAmount DESC

则表示在同一个客户的订单中，最新的订单（日期最新）会优先获得较小的行号。如果日期相同，金额更大的订单会排在前面。

ROW_NUMBER()

与

RANK()

、

DENSE_RANK()

有何不同？这三者都是窗口函数，都用于排名，但它们处理“并列”情况的方式截然不同，这常常是新手最容易混淆的地方。对我来说，理解它们的差异是掌握 SQL 窗口函数的入门课。

ROW_NUMBER()

: 就像上面提到的，它为每一行分配一个唯一的、连续的整数。即便有两行在排序条件上完全相同（即并列），

ROW_NUMBER()

也会给它们分配不同的、连续的数字。它不会跳过任何数字。如果你需要一个严格的、不重复的序列号，哪怕数据是重复的，

ROW_NUMBER()

就是你的首选。

RANK()

: 它会给并列的行分配相同的排名，并且会跳过下一个排名。例如，如果两个人并列第1名，那么接下来的排名会是第3名（跳过了第2名）。它反映的是“并列后的下一个有效位置”。

DENSE_RANK()

: 同样会给并列的行分配相同的排名，但它不会跳过下一个排名。例如，如果两个人并列第1名，那么接下来的排名会是第2名。它提供的是一个“紧密的”排名，没有空缺。我们用一个简单的学生分数表

Scores

来对比： StudentIDSubjectScore101Math90102Math85103Math90104Math85105Math80

SELECT
    StudentID,
    Subject,
    Score,
    ROW_NUMBER() OVER (PARTITION BY Subject ORDER BY Score DESC) AS rn,
    RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) AS rk,
    DENSE_RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) AS drk
FROM
    Scores
WHERE Subject = 'Math';

结果会是这样： StudentIDSubjectScorernrkdrk101Math90111103Math90211102Math85332104Math85432105Math80553 从结果可以看出：

rn

为每个 90 分的同学分配了不同的行号（1和2），接着是 85 分的 3和4。

rk

给两个 90 分的同学都排了 1，然后跳过 2，给两个 85 分的同学排了 3。

drk

给两个 90 分的同学排了 1，然后直接给两个 85 分的同学排了 2，没有跳过。选择哪个函数，完全取决于你对“排名”的定义。如果需要每个记录都有一个独一无二的序号，那就用

ROW_NUMBER()

。如何使用

ROW_NUMBER()

实现数据去重或分页？

ROW_NUMBER()

的两大杀手级应用就是数据去重和分页查询，尤其是在处理大量数据时，它的效率和灵活性是传统方法难以比拟的。 1. 数据去重 oracle知识库 oracle知识库下载下载在实际工作中，数据质量问题无处不在，重复数据是常态。

ROW_NUMBER()

提供了一种非常优雅且高效的去重方式。它的核心思想是：先定义什么构成“重复”，然后在这个“重复组”里，根据某个标准（比如最新时间、最大ID）选出你想要的那一条，其余的就“淘汰”。假设你有一个

UserLogins

表，记录了用户每次登录的信息，但由于系统bug或其他原因，同一个用户在同一秒内可能会产生多条几乎完全相同的登录记录，你只想要其中一条。 UserIDLoginTimeIPAddress12023-10-26 10:00:00192.168.1.112023-10-26 10:00:00192.168.1.122023-10-26 10:05:00192.168.1.222023-10-26 10:05:01192.168.1.2 去重的关键在于

PARTITION BY

哪些字段来定义“重复”，以及

ORDER BY

哪些字段来决定保留哪一条。

-- 查找重复数据并保留最新的或ID最小的
WITH DeduplicatedLogins AS (
    SELECT
        UserID,
        LoginTime,
        IPAddress,
        ROW_NUMBER() OVER (PARTITION BY UserID, LoginTime, IPAddress ORDER BY LoginTime DESC, IPAddress DESC) AS rn
        -- 这里 ORDER BY 的字段很重要，它决定了在完全重复的行中，哪一行会被标记为 rn=1
        -- 如果 LoginTime 和 IPAddress 也完全一致，可以考虑加一个自增ID字段来确保唯一性，例如 ORDER BY LoginTime DESC, LogID ASC
)
SELECT
    UserID,
    LoginTime,
    IPAddress
FROM
    DeduplicatedLogins
WHERE
    rn = 1;

通过将

rn = 1

的行筛选出来，我们就得到了每个“重复组”中我们想要的那一条记录，从而实现了数据的去重。这种方法比

DISTINCT

更灵活，因为它允许你在重复数据中选择特定的行。 2. 分页查询在 Web 应用中，分页是必不可少的功能。

ROW_NUMBER()

提供了一种可靠且高效的分页机制，尤其是在需要复杂排序或跨多列排序时。假设你需要从一个大表中获取第 N 页的数据，每页 M 条记录。

-- 获取 Products 表的第2页数据，每页10条（即获取第11到第20条记录）
WITH PagedProducts AS (
    SELECT
        ProductID,
        ProductName,
        Price,
        ROW_NUMBER() OVER (ORDER BY ProductID ASC) AS rn -- 假设我们按 ProductID 排序
    FROM
        Products
)
SELECT
    ProductID,
    ProductName,
    Price
FROM
    PagedProducts
WHERE
    rn BETWEEN 11 AND 20;

这里，

ROW_NUMBER()

首先为整个

Products

表的每一行分配一个行号。然后，外层查询通过

WHERE rn BETWEEN start_row_number AND end_row_number

来筛选出指定页的数据。这种方式在 SQL Server、Oracle、PostgreSQL 等数据库中都非常常见和高效。在实际项目中，使用

ROW_NUMBER()

可能会遇到哪些挑战或优化建议？尽管

ROW_NUMBER()

功能强大，但在实际应用中，尤其是在处理大规模数据集时，它并非没有“脾气”。我记得有一次，我花了好几个小时才发现一个

ROW_NUMBER()

的结果不对劲，最后才发现是

ORDER BY

少了一个关键字段，导致在同分情况下，结果变得随机。那种感觉真是... 所以，了解它的潜在挑战和优化策略非常重要。 1. 性能问题与索引挑战：当你在非常大的表上使用

ROW_NUMBER()

，特别是

PARTITION BY

和

ORDER BY

的列上没有合适的索引时，性能可能会急剧下降。数据库需要对数据进行排序和分组，这可能导致大量的磁盘 I/O 和 CPU 消耗。优化建议：确保

PARTITION BY

和

ORDER BY

子句中涉及的列上建有合适的索引。复合索引的效果通常更好，例如，如果你的

PARTITION BY

是

(CustomerID)

，

ORDER BY

是

(OrderDate DESC)

，那么一个在

(CustomerID, OrderDate DESC)

上的复合索引会非常有帮助。 2. 结果的确定性挑战：如果

ORDER BY

子句中的列值存在并列情况，且你没有提供足够的列来打破这些并列（即不能唯一确定行的顺序），那么

ROW_NUMBER()

分配的序号可能会是“不确定”的。这意味着每次执行相同的查询，对于并列的行，它们获得的

ROW_NUMBER()

可能会不同。这在去重场景下尤为危险，可能导致每次去重结果不一致。优化建议：始终在

ORDER BY

子句中包含一个能够唯一标识行的列（例如主键或一个自增ID），即使它不是你主要的排序依据。这能保证在所有其他排序条件都相同的情况下，每一行仍然有一个明确的、可重复的顺序。 3. 内存消耗挑战：窗口函数需要在内存中处理整个分区的数据。如果某个分区非常大，或者整个结果集非常大（当没有

PARTITION BY

时），这可能导致大量的内存消耗，甚至溢出到磁盘，从而影响性能。优化建议：尽量优化你的查询，减少需要处理的数据量。在

ROW_NUMBER()

之前先进行筛选（

WHERE

子句），或者只选择必要的列，都能有效降低内存压力。有时，将大表分批处理也是一种策略，尽管这会增加应用层面的复杂性。 4. 调试与理解挑战：当

ROW_NUMBER()

的结果不符合预期时，初学者往往会感到困惑。问题通常出在

PARTITION BY

或

ORDER BY

的逻辑上。优化建议：在调试时，先不加外层筛选，直接查询包含

ROW_NUMBER()

结果的 CTE 或子查询。这样你可以看到每个原始行是如何被分区和排序的，以及

ROW_NUMBER()

是如何分配的，这能帮助你快速定位问题。总的来说，

ROW_NUMBER()

是 SQL 中一个非常强大的工具，但要用好它，你需要深入理解其背后的原理，并结合实际数据特点来调整

PARTITION BY

和

ORDER BY

策略。

相关文章