PostgreSQL中的行编号 [英] Row numbering in PostgreSQL

问题描述

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

How to get row number in PostgreSQL when the results are ordered by some column?

例如

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

我认为查询将返回如下列表：

I supposed that the query would return list like this:

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

实际上我必须重复 ORDER 子句以使其起作用：

Actually i have to duplicate the ORDER clause into the query to make it functional:

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

还有其他方法如何在不重复代码的情况下返回有序和编号的结果吗？

Is there any other way how to return ordered and numbered results without necessity of duplicating the code?

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

I know this can be solved by incrementing some variable in the app itself, but i wanna do this at the database layer and return to the app already numbered results...

推荐答案

否-窗口函数中的code> order by 和 select order by 子句c>语句在功能上是两个不同的事物。

no - the order by in the windowing function and the order by clause of the select statement are functionally two different things.

此外，您的语句还会产生： ERROR：窗口函数调用需要OVER子句，因此：

Also, your statement produces: ERROR: window function call requires an OVER clause, so:

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

请注意，如果工资不是唯一的，则不能保证它们甚至会产生相同的顺序。也许会更好：

Note that if salaries are not unique then there is no guarantee that they will even produce the same order. Perhaps it would be better to do:

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

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

Also note that if you are running this query several times with different offsets, you need to:

1. 设置您的隔离级别可序列化


2. 确保您所订购的商品都是唯一的

1. set your isolation level to serializable
2. make sure that whatever you are ordering by is unique

，否则您可能会得到重复并缺少行。请参阅此答案原因。

or you may get duplicates and missing rows. See the comments on this answer for why.

这篇关于PostgreSQL中的行编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！