PostgreSQL 中的行编号

Question

当结果按某列排序时，如何在 PostgreSQL 中获取行号？

例如，

SELECT 30+row_number() AS position, * 
FROM users 
ORDER BY salary DESC 
LIMIT 30 
OFFSET 30

我认为查询将返回这样的列表：

position | name | salary
31       | Joy  | 4500
32       | Katie| 4000
33       | Frank| 3500

实际上，我必须将 ORDER 子句复制到查询中以使其起作用：

SELECT 30+row_number(ORDER BY salary DESC) AS position, * 
FROM users 
ORDER BY salary DESC 
LIMIT 30 
OFFSET 30

是否有其他方法可以返回有序和编号的结果，而无需复制代码？

我知道这可以通过增加应用程序本身中的一些变量来解决，但我想在数据库层执行此操作并返回到应用程序已经编号的结果...

Answer 1

否 - 窗口函数中的 order by 和 select 语句中的 order by 子句在功能上是两个不同的东西。

另外，您的语句会生成：错误：窗口函数调用需要 OVER 子句，因此：

SELECT 30+row_number(ORDER BY salary DESC) AS position, * FROM users ORDER BY salary DESC LIMIT 30 OFFSET 30

应该是：

SELECT 30+row_number() OVER(ORDER BY salary DESC) AS position, * FROM users ORDER BY salary DESC LIMIT 30 OFFSET 30

请注意，如果工资不唯一，则无法保证它们甚至会产生相同的订单。也许这样做会更好：

SELECT * 
FROM ( SELECT 30+row_number() OVER(ORDER BY salary DESC) AS position, * 
       FROM users )
ORDER BY position LIMIT 30 OFFSET 30

另请注意，如果您使用不同的偏移量多次运行此查询，则需要：

1. 设置您的 隔离级别 到可序列化，
2. 确保您订购的内容是唯一的

，否则可能会出现重复和丢失的行。请参阅 这个答案说明原因。