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

有没有其他方法可以返回已排序和编号的结果，而不需要复制代码？ 我知道可以通过在应用程序本身中递增某个变量来解决这个问题，但我想在数据库层面完成此操作，并返回已编号的结果给应用程序...

- Radek Simko

Answer 1

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

此外，您的语句会产生：ERROR: window function call requires an OVER clause，因此：

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. 确保您按照任何唯一的方式进行排序

否则，您可能会得到重复和缺失的行。请参见此答案上的评论以了解原因。

- user533832

---

"可重复读取的隔离级别"是必要的...但无论如何，您需要确保连接和事务保持打开状态...这可能取决于应用程序而非常困难。 - Chris Cogdon