postgresql SQL：将具有增量 ID 的列添加到 SELECT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32539438/
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
SQL: Add column with incremental id to SELECT
提问by michael
I have simple query like:
我有简单的查询，如：
SELECT name FROM people;
peopletable not have unique id column. I want to add to the query result column idwith incremental intstarting from 0/1 doesn't matter. How to achieve this? (postgresql DB)
people表没有唯一的 id 列。我想从 0/1 开始id增量添加到查询结果列int无关紧要。如何实现这一目标？(postgresql 数据库)
回答by Lukasz Szozda
Use ROW_NUMBER():
使用ROW_NUMBER()：
SELECT
name,
ROW_NUMBER() OVER (ORDER BY name) AS id
FROM people;
EDIT:
编辑：
Difference between ORDER BY 1vs ORDER BY column_name
ORDER BY 1VS之间的区别ORDER BY column_name
SELECT
name,
ROW_NUMBER() OVER (ORDER BY name) AS id
FROM people;
/* Execution Plan */
QUERY PLAN WindowAgg (cost=83.37..104.37 rows=1200 width=38)
-> Sort (cost=83.37..86.37 rows=1200 width=38)
**Sort Key: name**
-> Seq Scan on people (cost=0.00..22.00 rows=1200 width=38)
SELECT
name,
ROW_NUMBER() OVER (ORDER BY 1) AS id
FROM people;
/* Execution Plan */
QUERY PLAN WindowAgg (cost=0.00..37.00 rows=1200 width=38)
-> Seq Scan on people (cost=0.00..22.00 rows=1200 width=38)
In second case there is no sort operation.
在第二种情况下，没有排序操作。
You can also write second query as:
您还可以将第二个查询编写为：
SELECT
name,
ROW_NUMBER() OVER () AS id
FROM people;
Why people write ORDER BY 1in windowed functions?
为什么人们用ORDER BY 1窗口函数编写？
Because in some dialects it is required and ORDER BY 1acts like placeholder.
因为在某些方言中它是必需的并且ORDER BY 1就像占位符一样。
Oracle:
甲骨文：
ORA-30485: missing ORDER BY expression in the window specification
ORA-30485: 窗口规范中缺少 ORDER BY 表达式
SELECT
name,
ROW_NUMBER() OVER (ORDER BY 1) AS id
FROM people;
TSQL:
TSQL：
The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.
函数“ROW_NUMBER”必须有一个带 ORDER BY 的 OVER 子句。
SELECT
name,
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS id
FROM people;
回答by Arsen
Check out the row_number()function at https://www.postgresql.org/docs/current/static/functions-window.html
row_number()在https://www.postgresql.org/docs/current/static/functions-window.html查看函数
回答by Mureinik
The row_numberwindow function should fit the bill:
该row_number窗口的功能应符合该法案：
SELECT ROW_NUMBER() OVER (ORDER BY 1), *
FROM people
回答by Vamsi Prabhala
If you need it only when the query is run, you can use row_number.
如果您只在查询运行时需要它，您可以使用row_number.
select row_number() over(order by name) as id, name
from people
