SQL 如何在 PostgreSQL 查询中显示行号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3397121/
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
How to show row numbers in PostgreSQL query?
提问by vol7ron
I'd like to show the observation number for each record returned by a PostgreSQL query.
我想显示 PostgreSQL 查询返回的每条记录的观察编号。
I think in 8.4 windowing functions can perform this capability.
我认为在 8.4 中窗口函数可以执行此功能。
回答by vol7ron
select row_number() over (order by <field> nulls last) as rownum, *
from foo_tbl
order by <field>
If order is not necessary, this answer may also be simplified:
如果不需要顺序，这个答案也可以简化：
select row_number() over(), * -- notice: no fields are needed
from foo_tbl
SQL FiddleProof of Concept
SQL Fiddle概念验证
回答by vol7ron
Postgres < 8.4
Postgres < 8.4
For versions prior to 8.4:
对于 8.4 之前的版本：
SELECT count(*) rownum, foo.*
FROM datatable foo
JOIN datatable bar
ON (foo.pk_id <= bar.pk_id)
GROUP BY foo.pk_id, foo.a, foo.b
ORDER BY rownum
;
-- if there isn't a single unique/primary key field, you can concatenate fields
-- Example: ON (foo.a||foo.b||foo.c <= bar.a||bar.b||bar.c)
Hope this helps someone.
希望这可以帮助某人。
SQL FiddleProof of Concept
SQL Fiddle概念验证
Another Approach
另一种方法
I suggest avoiding this at all cost, but wanted to include it for posterity. It is somewhat expensive and I imagine does not scale well, but when a primary key does not exist on a table (bad db-design), your options may be limited. In most of those cases the recommendation is to perform numbering at the application layer.
我建议不惜一切代价避免这种情况，但希望将其包括在内。它有点贵，而且我认为扩展性不好，但是当表中不存在主键时（糟糕的数据库设计），您的选择可能会受到限制。在大多数情况下，建议在应用程序层执行编号。
-- Based on basic table w/o primary key
-- CREATE TABLE names ( name as text );
SELECT num, name[num]
FROM (
select generate_series( 1, (select count(*) from names) ) as num
) _nums,
(
select array_agg(name) as name from names
) _names
SQL FiddleProof of Concept
SQL Fiddle概念验证
Reasons it doesn't scale:
无法扩展的原因：
- packing your tuples into an array for each row is a bad idea
- 将你的元组打包成每一行的数组是一个坏主意
