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

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

為結果集的輸出編號。Numbers the output of a result set. 具體來說,傳回結果集分割區內某資料列的序號,序號從 1 開始,每個分割區第一個資料列的序號是 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. 如需詳細資訊,請參閱 決定性與非決定性函數For more information, see Deterministic and Nondeterministic Functions.

範例Examples

A.A. 簡單範例Simple examples

下列查詢會依字母順序傳回 4 個系統資料表。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. 並用 PARTITION 與 ROW_NUMBER()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 陳述式中的 SELECT 子句會依照 TerritoryName 排列整個查詢結果集。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. 並用 PARTITION 與 ROW_NUMBER()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)