PostgreSQL 中的行编号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5537525/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Row numbering in PostgreSQL
提问by Radek Simko
How to get row number in PostgreSQL when the results are ordered by some column?
当结果按某些列排序时，如何在 PostgreSQL 中获取行号？
e.g.
例如
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
Actually i have to duplicate the ORDERclause into the query to make it functional:
实际上，我必须将该ORDER子句复制到查询中以使其起作用：
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...
我知道这可以通过增加应用程序本身中的一些变量来解决，但我想在数据库层执行此操作并返回到应用程序已经编号的结果......
回答by
no - the order byin the windowing function and the order byclause of the selectstatement are functionally two different things.
否 -order by窗口函数中的in 和语句的order by子句在select功能上是两个不同的东西。
Also, your statement produces: ERROR: window function call requires an OVER clause, so:
此外，您的语句产生：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
should be:
应该：
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:
另请注意，如果您使用不同的偏移多次运行此查询，则需要：
- set your isolation levelto serializable
- make sure that whatever you are ordering by is unique
- 将隔离级别设置为可序列化
- 确保您订购的任何商品都是独一无二的
or you may get duplicates and missing rows. See the comments on this answerfor why.
或者你可能会得到重复和丢失的行。请参阅对此答案的评论以了解原因。
