sql - 在 SQL 中运行数字

Question

我有一个这样的 SQL 语句:

SELECT name FROM users WHERE deleted = 0;

如何在第一行创建一个带有运行编号的结果集？所以结果看起来像这样:

1 Name_1
2 Name_3
3 Name_12
4 Name_15
...

Answer 1

在 Oracle , SQL Server和 PostreSQL 8.4 :

SELECT  ROW_NUMBER() OVER (ORDER BY name)
FROM    users
WHERE   deleted = 0

在 MySQL :

SELECT  @r := @r + 1 AS rn
FROM    (
        SELECT  @r := 0
        ) vars, users
WHERE   deleted = 0
ORDER BY
        name

在 PostgreSQL 8.3 :

SELECT  num, (arr[num]).*
FROM    (
        SELECT  generate_series(1, array_upper(arr, 1)) AS num, arr
        FROM    (
                SELECT  ARRAY
                        (
                        SELECT  users
                        FROM    users
                        ORDER BY
                                name
                        ) AS arr
                ) q
        ) q2