代码之家 › 专栏 › 技术社区 › Brendan Long

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

tsql sql-server

Brendan Long · 技术社区 · 15 年前

SELECT SomeColumn, IncrementingNumbersFunction() FROM SomeTable

并让它返回:

SomeColumn | IncrementingNumbers
--------------------------------
some text  | 0
something  | 1
foo        | 2

5 回复 | 直到 15 年前

SQLMenace 15 年前

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

DOK 15 年前

我想你在找 行\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;

gbn 15 年前

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

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

laylarenee 9 年前

从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;