具有增量整数列的MSSQL Select语句...不是来自表 [英] MSSQL Select statement with incremental integer column... not from a table

问题描述

如果可能的话，我需要一个t-sql查询，该查询从任意表中返回值，并且还返回递增的整数列，其中第一行的值= 1，第二行的值为2，依此类推. >

此列实际上不存在于任何表中，并且必须严格地递增，因为ORDER BY子句可以对表的行进行排序，而我希望递增的行始终处于完美的形状...

谢谢.

-编辑 抱歉，忘记了，必须在SQL Server 2000上运行

解决方案

对于SQL 2005及更高版本

SELECT ROW_NUMBER() OVER( ORDER BY SomeColumn ) AS 'rownumber',*
    FROM YourTable

在2000年，您需要执行以下操作

SELECT IDENTITY(INT, 1,1) AS Rank ,VALUE
INTO #Ranks FROM YourTable WHERE 1=0

INSERT INTO #Ranks
SELECT SomeColumn  FROM YourTable
ORDER BY SomeColumn 

SELECT * FROM #Ranks
Order By Ranks

另请参见此处行号

I need, if possible, a t-sql query that, returning the values from an arbitrary table, also returns a incremental integer column with value = 1 for the first row, 2 for the second, and so on.

This column does not actually resides in any table, and must be strictly incremental, because the ORDER BY clause could sort the rows of the table and I want the incremental row in perfect shape always...

Thanks in advance.

--EDIT Sorry, forgot to mention, must run on SQL Server 2000

解决方案

For SQL 2005 and up

SELECT ROW_NUMBER() OVER( ORDER BY SomeColumn ) AS 'rownumber',*
    FROM YourTable

for 2000 you need to do something like this

SELECT IDENTITY(INT, 1,1) AS Rank ,VALUE
INTO #Ranks FROM YourTable WHERE 1=0

INSERT INTO #Ranks
SELECT SomeColumn  FROM YourTable
ORDER BY SomeColumn 

SELECT * FROM #Ranks
Order By Ranks

see also here Row Number

这篇关于具有增量整数列的MSSQL Select语句...不是来自表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！