SQL:对 SELECT 语句返回的行进行编号 [英] SQL: Numbering the rows returned by a SELECT statement

问题描述

假设我有一个返回一些结果集的 SELECT 语句.有什么方法可以按以下方式对结果进行编号:

Suppose I have a SELECT statement that returns some set of results. Is there some way I can number my results in the following way:

从 PuppyNames ORDER BY NumberOfVotes 中选择前 3 个名字

SELECT TOP 3 Name FROM PuppyNames ORDER BY NumberOfVotes

会给我...

菲多

流浪者

弗莱迪克鲁格

...但我想要...

1，菲多

2、路虎

3、弗莱迪·克鲁格

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

where of course the commas signify that the numbers are in their own column. [I am using SQL Server 2000.]

推荐答案

在 Microsoft SQL Server 2005 中，您拥有 ROW_NUMBER() 函数，它完全符合您的要求.

In Microsoft SQL Server 2005, you have the ROW_NUMBER() function which does exactly what you want.

如果您坚持使用 SQL Server 2000，典型的技术是创建一个新的临时表来包含您的查询结果，并添加一个 IDENTITY 列并生成增量值.在此处查看讨论此技术的文章:http://www.databasejournal.com/features/mssql/article.php/3572301/RowNumber-function-in-SQL-Server-2005.htm

If you are stuck with SQL Server 2000, the typical technique was to create a new temporary table to contain the result of your query, plus add an IDENTITY column and generate incremental values. See an article that talks about this technique here: http://www.databasejournal.com/features/mssql/article.php/3572301/RowNumber-function-in-SQL-Server-2005.htm

这篇关于SQL:对 SELECT 语句返回的行进行编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！