SQL：将具有增量ID的列添加到SELECT [英] SQL: Add column with incremental id to SELECT

问题描述

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

I have simple query like:

SELECT name FROM people;

people 表没有唯一的ID列。我想在查询结果列 id 中添加从0/1开始的增量 int 没关系。如何实现呢？ （PostgreSQL数据库）

people table not have unique id column. I want to add to the query result column id with incremental int starting from 0/1 doesn't matter. How to achieve this? (postgresql DB)

推荐答案

使用 ROW_NUMBER（）：

SQLFiddle

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

编辑：

ORDER BY 1 与 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)

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

In second case there is no sort operation.

您还可以编写第二个查询，例如：

You can also write second query as:

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

为什么人们写 ORDER BY 1

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

Because in some dialects it is required and ORDER BY 1 acts like placeholder.

Oracle：

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

ORA-30485: missing ORDER BY expression in the window specification

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

TSQL：

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

The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

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

这篇关于SQL：将具有增量ID的列添加到SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！