给结果记录添加自动生成的ID

Question

我需要在查询结果中添加自动生成的自增ID。例如，对于以下查询：

SELECT TOP 3 Users.Reputation FROM Users ORDER BY 1 DESC

而不是获得

• 101
• 100
• 99

我想要获得

• 1, 101
• 2, 100
• 3, 99

怎样能够做到呢？

- Yossale

---

我正在开发数据浏览器，所以应该是普通的MS SQL。 - Yossale

Answer 1

如果它是SQL Server，最简单的方法是使用以下代码：

SELECT TOP 3 
ROW_NUMBER() OVER(ORDER BY Users.Reputation DESC), 
Users.Reputation 
FROM Users

更新：

我检查了查询执行计划，并发现内部的order by，它定义了结果集中每个分区内行的逻辑顺序（在这种情况下，我没有使用指定的分区条件），使用了Sort步骤，这与常规排序相同：

SELECT Users.Reputation
FROM Users
ORDER BY Users.Reputation DESC

这一步完成后，有两个有趣的步骤：分段和序列项目。第二个步骤说明它是在对一个有序数据集进行计算。

最后的步骤是TOP和SELECT。

我对缺点很感兴趣，但看起来还可以。

最后需要注意的是：使用正常的OrderBy和TOP：

 SELECT TOP 3 Users.Reputation
    FROM Users
    ORDER BY Users.Reputation DESC

排序更容易，但几乎一样：排序（前N个排序）。

- András Ottó

Answer 2

怎么样：

;WITH CTE AS 
(SELECT
    RowNum = ROW_NUMBER() OVER(ORDER BY Reputation DESC),
    Users.Reputation
 FROM
    Users
)
SELECT TOP 3
    RowNum, Reputation
FROM
    CTE
ORDER BY  
    RowNum

此 CTE（公共表达式） 可用于 SQL Server 2005及更新版本，结合ROW_NUMBER() 排名函数一起使用，该函数也适用于 2005及更新版本。

它为下一个语句创建了一个“即时”视图，并允许在选择之前进行一些额外的处理。 ROW_NUMBER() 仅在 OVER (ORDER BY ....) 子句中定义的顺序中向每行添加连续数字。 因此，第一行得到 #1，第二行得到#2，以此类推。

点击链接了解更多关于CTE的内容：MSDN - Using Common Table Expressions

点击链接了解更多关于 ROW_NUMBER 和其他排名函数的内容：MSDN ROW_NUMBER

- marc_s

---

想知道这是否是 MSSQL 2005 解决方案或始终有效？ - Vikdor

@Vikdor：适用于大多数现代DBMS（尽管其他DBMS需要在末尾而不是开头使用语句终止符）。 - user330315

这是否有点过度设计了呢？一个简单的SELECT语句只需要一个OVER（ORDER BY...）子句就可以实现。 - András Ottó

@AndrásOttó：尝试在您的解决方案中添加一个ORDER BY，按行号排序！ :-) 我发现将所有内容包装到CTE中，然后能够使用CTE定义的列按名称更容易 - marc_s

但这是一个好的提示，我会尝试找到任何关于它的文档，因为我过去只是使用它。 - András Ottó