SQL：为SELECT语句返回的行进行编号

Question

假设我有一个SELECT语句，返回一些结果集。是否有办法按以下方式编号我的结果：

SELECT TOP 3 Name FROM PuppyNames ORDER BY NumberOfVotes

将会给我...

Fido

Rover

Freddy Krueger

...但是我想要...

1, Fido

2, Rover

3, Freddy Krueger

当然逗号表示数字在它们自己的列中。[我正在使用SQL Server 2000.]

- Jake

---

你的 ORDER BY 子句可能需要使用 DESC 修饰符。 - BoltBait

Answer 1

在Microsoft SQL Server 2005中，您可以使用ROW_NUMBER()函数来完成您想要的操作。

如果您被限制在SQL Server 2000上，则通常的方法是创建一个新的临时表来包含查询结果，并添加一个IDENTITY列并生成递增值。请参阅此处讨论该技术的文章：http://www.databasejournal.com/features/mssql/article.php/3572301/RowNumber-function-in-SQL-Server-2005.htm。

- Bill Karwin

Answer 2

如果使用SQL 2000，您需要使用相关子查询。

   SELECT (
              SELECT COUNT(*)
                FROM PuppyNames b
               WHERE b.Popularity <= a.Popularity
          ) AS Ranking
        , a.Name
     FROM PuppyNames a
 ORDER BY a.Popularity

- esabine

---

如果两个或更多记录具有相同的流行度，则此方法无效。通常，它提供排名而不是编号。 - Panos

Answer 3

通常在客户端应用程序中添加数字更容易。虽然在SQL中有一些技巧，但对于纯粹主义者来说，它们涉及作弊，并且通常不可移植。

对我来说，这是最基本的重构模式之一。

- doofledorfer

Answer 4

你也可以使用临时表来完成：

SELECT TOP 3 Name FROM PuppyNames ORDER BY NumberOfVotes DESC

变成

CREATE TABLE #RowNumberTable (
    RowNumber int IDENTITY (1,1),
    PuppyName varchar(MAX)
)
INSERT #RowNumberTable (PuppyName)
SELECT TOP 3 Name FROM PuppyNames ORDER BY NumberOfVotes DESC
SELECT * from #RowNumberTable ORDER BY RowNumber
DROP TABLE #RowNumberTable

请注意，你的SELECT语句已经在其中。只是被一些内容包围使得行号能够正常工作。

- BoltBait

Answer 5

您可以使用此查询，该查询考虑了主键以便在 NumberOfVotes 相同时提供正确的编号。

SELECT TOP 3 COUNT(*) AS Number, p1.Name
FROM PuppyNames AS p1 INNER JOIN PuppyNames AS p2 
    ON p1.NumberOfVotes < p2.NumberOfVotes OR (p1.NumberOfVotes = p2.NumberOfVotes AND p1.ID >= p2.ID)
GROUP BY p1.Name
ORDER BY Number

- Panos

Answer 6

SQL 2005, 2008:

SELECT TOP 3 ROW_NUMBER() OVER(ORDER BY NumberOfVotes DESC) AS VoteRank, Name FROM PuppyNames

编辑：抱歉 - 刚看到您指定了2000。

- MarkB

---

哇，2000年没有分析函数？ - Mark Brady

我认为分析函数直到2003年才成为ANSI标准的一部分。 - quillbreaker