ROW_NUMBER (Transact-SQL)
-
适用于:
SQL Server(从 2008 开始)
Azure SQL 数据库
Azure SQL 数据仓库
并行数据仓库
对结果集的输出进行编号。 具体来说,返回结果集分区内行的序列号,每个分区的第一行从 1 开始。
ROW_NUMBER
和RANK
类似。ROW_NUMBER
按顺序对所有行进行编号(例如 1、2、3、4、5)。RANK
为关系提供相同的数值(例如 1、2、2、4、5)。备注
ROW_NUMBER
是运行查询时计算出的临时值。 若要将数值保存在表中,请参阅 IDENTITY 属性和 SEQUENCE。语法
复制
ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )
参数
PARTITION BY value_expression
将 FROM 子句生成的结果集划分为应用 ROW_NUMBER 函数的分区。 value_expression 指定对结果集进行分区所依据的列 。 如果未指定PARTITION BY
,则此函数将查询结果集的所有行视为单个组。有关详细信息,请参阅 OVER 子句 (Transact-SQL)。order_by_clause
ORDER BY
子句可确定在特定分区中为行分配唯一ROW_NUMBER
的顺序。 它是必需的。 有关详细信息,请参阅 OVER 子句 (Transact-SQL)。返回类型
bigint
一般备注
除非以下条件成立,否则不保证在每次执行时,使用
ROW_NUMBER()
的查询所返回行的顺序都完全相同。-
分区列的值是唯一的。
-
ORDER BY
列的值是唯一的。 -
分区列和
ORDER BY
列的值的组合是唯一的。
ROW_NUMBER()
具有不确定性。 有关详细信息,请参阅 Deterministic and Nondeterministic Functions。示例
A. 简单示例
以下查询按字母顺序返回四个系统表。
SQL复制
SELECT name, recovery_model_desc FROM sys.databases WHERE database_id < 5 ORDER BY name ASC;
下面是结果集:
NAME recovery_model_desc master SIMPLE model FULL msdb SIMPLE tempdb SIMPLE 要在每行的前面添加一个行编号列,请使用
ROW_NUMBER
函数添加一个列(此示例中名为Row#
)。必须将ORDER BY
子句向前移动到OVER
子句处。SQL复制
SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#, name, recovery_model_desc FROM sys.databases WHERE database_id < 5;
下面是结果集:
Row# NAME recovery_model_desc 1 master SIMPLE 2 model FULL 3 msdb SIMPLE 4 tempdb SIMPLE 若是在
recovery_model_desc
列上添加PARTITION BY
子句,当recovery_model_desc
值发生更改时将重新开始编号。SQL复制
SELECT ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC) AS Row#, name, recovery_model_desc FROM sys.databases WHERE database_id < 5;
下面是结果集:
Row# NAME recovery_model_desc 1 model FULL 1 master SIMPLE 2 msdb SIMPLE 3 tempdb SIMPLE B. 返回销售人员的行号
以下示例根据销售人员年初至今的销售额,计算 Adventure Works Cycles 中销售人员的行号。
SQL复制
USE AdventureWorks2012; GO SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row, FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD" FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;
下面是结果集:
复制
Row FirstName LastName SalesYTD --- ----------- ---------------------- ----------------- 1 Linda Mitchell 4251368.54 2 Jae Pak 4116871.22 3 Michael Blythe 3763178.17 4 Jillian Carson 3189418.36 5 Ranjit Varkey Chudukatil 3121616.32 6 José Saraiva 2604540.71 7 Shu Ito 2458535.61 8 Tsvi Reiter 2315185.61 9 Rachel Valdez 1827066.71 10 Tete Mensa-Annan 1576562.19 11 David Campbell 1573012.93 12 Garrett Vargas 1453719.46 13 Lynn Tsoflias 1421810.92 14 Pamela Ansman-Wolfe 1352577.13
C. 返回行的子集
下面的示例按
SalesOrderHeader
的顺序计算OrderDate
表中所有行的行号,并只返回行50
到60
(含)。SQL复制
USE AdventureWorks2012; GO WITH OrderedOrders AS ( SELECT SalesOrderID, OrderDate, ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber FROM Sales.SalesOrderHeader ) SELECT SalesOrderID, OrderDate, RowNumber FROM OrderedOrders WHERE RowNumber BETWEEN 50 AND 60;
D. 将 ROW_NUMBER () 与 PARTITION 一起使用
以下示例使用
PARTITION BY
参数按列TerritoryName
对结果集进行分区。 在ORDER BY
子句中指定的OVER
子句按列SalesYTD
对每个分区中的行进行排序。ORDER BY
语句中的SELECT
按TerritoryName
子句对整个查询结果集进行排序。SQL复制
USE AdventureWorks2012; GO SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1) AS SalesYTD, ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS Row FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0 ORDER BY TerritoryName;
下面是结果集:
复制
FirstName LastName TerritoryName SalesYTD Row --------- -------------------- ------------------ ------------ --- Lynn Tsoflias Australia 1421810.92 1 José Saraiva Canada 2604540.71 1 Garrett Vargas Canada 1453719.46 2 Jillian Carson Central 3189418.36 1 Ranjit Varkey Chudukatil France 3121616.32 1 Rachel Valdez Germany 1827066.71 1 Michael Blythe Northeast 3763178.17 1 Tete Mensa-Annan Northwest 1576562.19 1 David Campbell Northwest 1573012.93 2 Pamela Ansman-Wolfe Northwest 1352577.13 3 Tsvi Reiter Southeast 2315185.61 1 Linda Mitchell Southwest 4251368.54 1 Shu Ito Southwest 2458535.61 2 Jae Pak United Kingdom 4116871.22 1
示例:Azure SQL 数据仓库 和 并行数据仓库
E. 返回销售人员的行号
以下示例根据销售代表所分配的销售配额返回各自的
ROW_NUMBER
。SQL复制
-- Uses AdventureWorks SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber, FirstName, LastName, CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota FROM dbo.DimEmployee AS e INNER JOIN dbo.FactSalesQuota AS sq ON e.EmployeeKey = sq.EmployeeKey WHERE e.SalesPersonFlag = 1 GROUP BY LastName, FirstName;
以下为部分结果集。
复制
RowNumber FirstName LastName SalesQuota --------- --------- ------------------ ------------- 1 Jillian Carson 12,198,000.00 2 Linda Mitchell 11,786,000.00 3 Michael Blythe 11,162,000.00 4 Jae Pak 10,514,000.00
F. 将 ROW_NUMBER () 与 PARTITION 一起使用
以下示例显示了将
ROW_NUMBER
函数与PARTITION BY
参数结合使用的情况。 这样会让ROW_NUMBER
函数对每个分区中的行进行编号。SQL复制
-- Uses AdventureWorks SELECT ROW_NUMBER() OVER(PARTITION BY SalesTerritoryKey ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber, LastName, SalesTerritoryKey AS Territory, CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota FROM dbo.DimEmployee AS e INNER JOIN dbo.FactSalesQuota AS sq ON e.EmployeeKey = sq.EmployeeKey WHERE e.SalesPersonFlag = 1 GROUP BY LastName, FirstName, SalesTerritoryKey;
以下为部分结果集。
复制
RowNumber LastName Territory SalesQuota --------- ------------------ --------- ------------- 1 Campbell 1 4,025,000.00 2 Ansman-Wolfe 1 3,551,000.00 3 Mensa-Annan 1 2,275,000.00 1 Blythe 2 11,162,000.00 1 Carson 3 12,198,000.00 1 Mitchell 4 11,786,000.00 2 Ito 4 7,804,000.00
另请参阅
RANK (Transact-SQL)
DENSE_RANK (Transact-SQL)
NTILE (Transact-SQL) -