SQL：将具有增量 id 的列添加到 SELECT

我有一个简单的查询，例如：

SELECT name FROM people;

The people表没有唯一的 id 列。我想在查询结果中添加一列id与增量int从 0 或 1 开始（无所谓）。如何才能实现这一目标呢？ （postgresql 数据库）

Use ROW_NUMBER():

SQLFiddle http://sqlfiddle.com/#!15/b5bb81/2/0

SELECT 
  name,
  ROW_NUMBER() OVER (ORDER BY name) AS id
FROM people;

EDIT:

之间的区别ORDER BY 1 vs ORDER BY column_name

SQLFiddle演示 http://sqlfiddle.com/#!15/a227e/4/1

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)

在第二种情况下，没有排序操作。

您还可以将第二个查询编写为：

SELECT 
    name,
    ROW_NUMBER() OVER () AS id
FROM people;

人们为什么写作ORDER BY 1在窗口函数中？

因为在某些方言中这是必需的并且ORDER BY 1其作用类似于占位符。

Oracle:

ORA-30485: 窗口规范中缺少 ORDER BY 表达式

SELECT 
  name,
  ROW_NUMBER() OVER (ORDER BY 1) AS id
FROM people;

TSQL:

函数“ROW_NUMBER”必须具有带有 ORDER BY 的 OVER 子句。

SELECT 
    name,
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS id
FROM people;