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 rnFROM 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 来对比：
101Math90102Math85103Math90104Math85105Math80
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 drkFROM ScoresWHERE Subject = 'Math';
结果会是这样：
101Math90111103Math90211102Math85332104Math85432105Math80553
从结果可以看出：
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. 数据去重
百度文心百中
百度大模型语义搜索体验中心
22 查看详情
在实际工作中，数据质量问题无处不在，重复数据是常态。ROW_NUMBER() 提供了一种非常优雅且高效的去重方式。它的核心思想是：先定义什么构成“重复”，然后在这个“重复组”里，根据某个标准（比如最新时间、最大ID）选出你想要的那一条，其余的就“淘汰”。
假设你有一个 UserLogins 表，记录了用户每次登录的信息，但由于系统bug或其他原因，同一个用户在同一秒内可能会产生多条几乎完全相同的登录记录，你只想要其中一条。
12023-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, IPAddressFROM DeduplicatedLoginsWHERE 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, PriceFROM PagedProductsWHERE 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 策略。
以上就是sql 中 row_number 用法_sql 中 row_number 行号生成指南的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/604506.html
微信扫一扫 
支付宝扫一扫 
