函数在SQL Server中生成递增数字

ROW_NUMBER()

SELECT SomeColumn, 
     ROW_NUMBER() OVER(Order by SomeColumn) as IncrementingNumbers
 FROM SomeTable

在SQL Server 2000上,可以使用标识,但如果有删除,则会有间隙

SELECT SomeColumn, 
     IDENTITY( INT,1,1) AS IncrementingNumbers
     INTO #temp
 FROM SomeTable
 ORDER BY SomeColumn


 SELECT * FROM #temp
 ORDER BY IncrementingNumbers

我想你在找 行\u编号

From MSDN (如果还有更多)下面的示例返回AdventureWorks2008R2中销售人员基于今年迄今的销售额的行编号。

SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode 

FROM Sales.vSalesPerson

WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

你可以用一个自动递增的标识列,还是我不明白这个问题?

http://msdn.microsoft.com/en-us/library/Aa933196

从SQL 2012及更高版本开始,有一个 built-in sequence feature .

例子:

-- sql to create the Sequence object
CREATE SEQUENCE dbo.MySequence
    AS int  
    START WITH 1  
    INCREMENT BY 1 ;  
GO

-- use the sequence
SELECT NEXT VALUE FOR dbo.MySequence;
SELECT NEXT VALUE FOR dbo.MySequence;
SELECT NEXT VALUE FOR dbo.MySequence;

不,SQLServer中没有序列生成函数。你可能会发现 identity 字段类型可方便地解决当前问题。