ROW_NUMBER (Transact-SQL)ROW_NUMBER (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

对结果集的输出进行编号。Numbers the output of a result set. 具体来说,返回结果集分区内行的序列号,每个分区的第一行从 1 开始。More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

ROW_NUMBERRANK 类似。ROW_NUMBER and RANK are similar. ROW_NUMBER 按顺序对所有行进行编号(例如 1、2、3、4、5)。ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK 为关系提供相同的数值(例如 1、2、2、4、5)。RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5).

备注

ROW_NUMBER 是运行查询时计算出的临时值。ROW_NUMBER is a temporary value calculated when the query is run. 若要将数值保存在表中,请参阅 IDENTITY 属性SEQUENCETo persist numbers in a table, see IDENTITY Property and SEQUENCE.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )  

参数Arguments

PARTITION BY value_expressionPARTITION BY value_expression
FROM 子句生成的结果集划分为应用 ROW_NUMBER 函数的分区。Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. value_expression 指定对结果集进行分区所依据的列。value_expression specifies the column by which the result set is partitioned. 如果未指定 PARTITION BY,则此函数将查询结果集的所有行视为单个组。If PARTITION BY is not specified, the function treats all rows of the query result set as a single group. 有关详细信息,请参阅 OVER 子句 (Transact-SQL)For more information, see OVER Clause (Transact-SQL).

order_by_clauseorder_by_clause
ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. 它是必需的。It is required. 有关详细信息,请参阅 OVER 子句 (Transact-SQL)For more information, see OVER Clause (Transact-SQL).

返回类型Return Types

bigintbigint

一般备注General Remarks

除非以下条件成立,否则不保证在每次执行时,使用 ROW_NUMBER() 的查询所返回行的顺序都完全相同。There is no guarantee that the rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution unless the following conditions are true.

  1. 分区列的值是唯一的。Values of the partitioned column are unique.

  2. ORDER BY 列的值是唯一的。Values of the ORDER BY columns are unique.

  3. 分区列和 ORDER BY 列的值的组合是唯一的。Combinations of values of the partition column and ORDER BY columns are unique.

ROW_NUMBER() 具有不确定性。ROW_NUMBER() is nondeterministic. 有关详细信息,请参阅 Deterministic and Nondeterministic FunctionsFor more information, see Deterministic and Nondeterministic Functions.

示例Examples

A.A. 简单示例Simple examples

以下查询按字母顺序返回四个系统表。The following query returns the four system tables in alphabetic order.

SELECT 
  name, recovery_model_desc
FROM sys.databases 
WHERE database_id < 5
ORDER BY name ASC;

下面是结果集:Here is the result set.

NAMEname recovery_model_descrecovery_model_desc
mastermaster SIMPLESIMPLE
modelmodel FULLFULL
msdbmsdb SIMPLESIMPLE
tempdbtempdb SIMPLESIMPLE

要在每行的前面添加一个行编号列,请使用 ROW_NUMBER 函数添加一个列(此示例中名为 Row#)。To add a row number column in front of each row, add a column with the ROW_NUMBER function, in this case named Row#. 必须将 ORDER BY 子句向前移动到 OVER 子句处。You must move the ORDER BY clause up to the OVER clause.

SELECT 
  ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,
  name, recovery_model_desc
FROM sys.databases 
WHERE database_id < 5;

下面是结果集:Here is the result set.

Row#Row# NAMEname recovery_model_descrecovery_model_desc
11 mastermaster SIMPLESIMPLE
22 modelmodel FULLFULL
33 msdbmsdb SIMPLESIMPLE
44 tempdbtempdb SIMPLESIMPLE

若是在 recovery_model_desc 列上添加 PARTITION BY 子句,当 recovery_model_desc 值发生更改时将重新开始编号。Adding a PARTITION BY clause on the recovery_model_desc column, will restart the numbering when the recovery_model_desc value changes.

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;

下面是结果集:Here is the result set.

Row#Row# NAMEname recovery_model_descrecovery_model_desc
11 modelmodel FULLFULL
11 mastermaster SIMPLESIMPLE
22 msdbmsdb SIMPLESIMPLE
33 tempdbtempdb SIMPLESIMPLE

B.B. 返回销售人员的行号Returning the row number for salespeople

以下示例根据销售人员年初至今的销售额,计算 Adventure Works CyclesAdventure Works Cycles 中销售人员的行号。The following example calculates a row number for the salespeople in Adventure Works CyclesAdventure Works Cycles based on their year-to-date sales ranking.

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;  

下面是结果集:Here is the result set.

  
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.C. 返回行的子集Returning a subset of rows

下面的示例按 SalesOrderHeader 的顺序计算 OrderDate 表中所有行的行号,并只返回行 5060(含)。The following example calculates row numbers for all rows in the SalesOrderHeader table in the order of the OrderDate and returns only rows 50 to 60 inclusive.

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.D. 将 ROW_NUMBER () 与 PARTITION 一起使用Using ROW_NUMBER() with PARTITION

以下示例使用 PARTITION BY 参数按列 TerritoryName 对结果集进行分区。The following example uses the PARTITION BY argument to partition the query result set by the column TerritoryName. ORDER BY 子句中指定的 OVER 子句按列 SalesYTD 对每个分区中的行进行排序。The ORDER BY clause specified in the OVER clause orders the rows in each partition by the column SalesYTD. ORDER BY 语句中的 SELECTTerritoryName 子句对整个查询结果集进行排序。The ORDER BY clause in the SELECT statement orders the entire query result set by TerritoryName.

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;  

下面是结果集:Here is the result set.

  
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 数据仓库Azure SQL Data Warehouse并行数据仓库Parallel Data WarehouseExamples: Azure SQL 数据仓库Azure SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse

E.E. 返回销售人员的行号Returning the row number for salespeople

以下示例根据销售代表所分配的销售配额返回各自的 ROW_NUMBERThe following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.

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

以下为部分结果集。Here is a partial result set.


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.F. 将 ROW_NUMBER () 与 PARTITION 一起使用Using ROW_NUMBER() with PARTITION

以下示例显示了将 ROW_NUMBER 函数与 PARTITION BY 参数结合使用的情况。The following example shows using the ROW_NUMBER function with the PARTITION BY argument. 这样会让 ROW_NUMBER 函数对每个分区中的行进行编号。This causes the ROW_NUMBER function to number the rows in each partition.

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

以下为部分结果集。Here is a partial result set.

 
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  

另请参阅See Also

RANK (Transact-SQL) RANK (Transact-SQL)
DENSE_RANK (Transact-SQL) DENSE_RANK (Transact-SQL)
NTILE (Transact-SQL)NTILE (Transact-SQL)