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

Question

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

SELECT name FROM people;

该people表没有唯一ID列。我想添加到查询结果列id增量int从0或1开始（这并不重要）。一个人怎么能做到这一点？（PostgreSQL数据库）

Answer 1

用途ROW_NUMBER()：

SQLFiddle

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

编辑：

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

SQLFiddleDemo

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充当占位符。

甲骨文：

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;