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

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 来对比：

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';

结果会是这样：

从结果可以看出：

• 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. 数据去重

PixVerse

PixVerse是一款强大的AI视频生成工具，可以轻松地将多种输入转化为令人惊叹的视频。

下载

在实际工作中，数据质量问题无处不在，重复数据是常态。ROW_NUMBER() 提供了一种非常优雅且高效的去重方式。它的核心思想是：先定义什么构成“重复”，然后在这个“重复组”里，根据某个标准（比如最新时间、最大ID）选出你想要的那一条，其余的就“淘汰”。

假设你有一个 UserLogins 表，记录了用户每次登录的信息，但由于系统bug或其他原因，同一个用户在同一秒内可能会产生多条几乎完全相同的登录记录，你只想要其中一条。

去重的关键在于 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 策略。