SQL Server 2005引入了ROW_NUMBER()函数。通过该函数,可以在记录集中方便的增加行号。
以下为在MSDN的解释:
ROW_NUMBER (Transact-SQL)
Updated: 17 July 2006
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Syntax
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
Arguments
-
<
partition_by_clause>
-
Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. For the syntax of PARTITION BY, see OVER Clause (Transact-SQL).
-
<
order_by_clause>
-
Determines the order in which the ROW_NUMBER value is assigned to the rows in a partition. For more information, see ORDER BY Clause (Transact-SQL). An integer cannot represent a column when the <order_by_clause> is used in a ranking function.
Return Types
bigint
Remarks
The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.
Examples
Note:
The ORDER BY in the OVER clause orders ROW_NUMBER. If you add an ORDER BY clause to the SELECT statement that orders by a column(s) other than 'Row Number' the result set will be ordered by the outer ORDER BY.
The following example returns the ROW_NUMBER for the salespeople in AdventureWorks based on the year-to-date sales.
USE AdventureWorks; GO SELECT c.FirstName, c.LastName ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number' ,s.SalesYTD, a.PostalCode FROM Sales.SalesPerson s INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID INNER JOIN Person.Address a ON a.AddressID = c.ContactID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;
The following example returns rows with numbers 50 to 60 inclusive in the order of the OrderDate.
USE AdventureWorks; GO WITH OrderedOrders AS ( SELECT SalesOrderID, OrderDate, ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber' FROM Sales.SalesOrderHeader ) SELECT * FROM OrderedOrders WHERE RowNumber BETWEEN 50 AND 60;
The following example shows using the PARTITION BY argument.
USE AdventureWorks; GO SELECT c.FirstName, c.LastName ,ROW_NUMBER() OVER (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number' ,s.SalesYTD, a.PostalCode FROM Sales.SalesPerson s INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID INNER JOIN Person.Address a ON a.AddressID = c.ContactID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;