row_number() 是窗口函数，通过为结果集按确定性排序分配唯一递增序号，再结合子查询或cte筛选序号范围，实现稳定分页；它避免了offset/limit在数据变更时的重复或跳过问题，且要求order by含唯一列保证稳定性。
ROW_NUMBER() 是什么，为什么它能解决分页问题
直接说结论：用 ROW_NUMBER() 给查询结果加序号，再套一层子查询或 CTE 过滤范围，就能实现稳定、可复用的分页。它不像 LIMIT/OFFSET 那样在数据频繁变更时导致重复或跳过记录——因为序号是基于当前查询快照生成的，不依赖物理位置。
典型场景是后台列表页需要“第 3 页，每页 20 条”，且排序字段（比如 created_at）存在大量相同值；这时 OFFSET 40 LIMIT 20 可能因并发插入/更新造成翻页错位，而 ROW_NUMBER() 结合确定性排序（如加 id 作第二排序键）就能避免。
怎么写一个带排名的分页查询
核心结构是：先用窗口函数生成行号，再在外层 WHERE 筛选区间。注意必须包含明确的 ORDER BY 子句，否则 PostgreSQL 会报错：window function requires an ORDER BY clause。
- 基础写法（子查询）：
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC, id DESC) AS rn FROM orders ) t WHERE rn BETWEEN 41 AND 60;
- 更清晰的写法（CTE）：
WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY status, updated_at DESC, id) AS rn FROM tasks ) SELECT id, status, updated_at FROM ranked WHERE rn BETWEEN 1 AND 20;
- 别漏掉排序稳定性：如果
status值大量重复，只按它排序会导致ROW_NUMBER()分配顺序不确定（即使结果看起来一致），务必补上唯一列（如id）作为决胜排序键
ROW_NUMBER() 和 RANK()、DENSE_RANK() 的关键区别
三者都生成序号，但处理并列的方式完全不同，直接影响分页逻辑是否“连续”：
-
ROW_NUMBER()：严格递增，1, 2, 3, 4… 即使值相同也绝不重复 —— 最适合分页，因为每行都有唯一序号 -
RANK()：并列时占位，比如值相同排前两名，则下一个是 4（跳过 3）—— 会导致WHERE rn = 3查不到数据 -
DENSE_RANK()：并列不占位，1, 1, 2, 2, 3… —— 序号不唯一，无法用作分页索引
所以分页只认 ROW_NUMBER()，其他两个函数更适合做“排行榜名次展示”这类业务需求。
性能和索引注意事项
窗口函数本身不走索引，ROW_NUMBER() 必须扫描并排序全部符合条件的行，所以性能瓶颈往往在 OVER () 里的 ORDER BY 字段上。
- 确保
ORDER BY列有复合索引，例如：CREATE INDEX idx_orders_sort ON orders (created_at DESC, id DESC); - 不要在大表上对无过滤条件的全表跑
ROW_NUMBER()，先用WHERE缩小数据集再排名 - 如果只是要总数+当前页数据，别用
COUNT(*) OVER()，它会强制物化全部结果；改用单独SELECT COUNT(*)查询总条数更高效
真正难的是让排序既稳定又快——排序字段的选择、索引覆盖度、是否允许业务侧接受轻微延迟一致性，这些比函数语法本身更影响落地效果。
