ROW_NUMBER (Transact-SQL)

ROW_NUMBER ( ) 

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

除非以下条件成立，否则不保证在每次执行时，使用 ROW_NUMBER() 的查询所返回行的顺序完全相同。

以下示例根据销售人员年初至今的销售额，计算 Adventure Works Cycles 中销售人员的行号。

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

下面的示例按 OrderDate 的顺序计算 SalesOrderHeader 表中所有行的行号，并只返回行 50 到 60（含）。

以下示例使用 PARTITION BY 参数按列 TerritoryName 对结果集进行分区。 在 OVER 子句中指定的 ORDER BY 子句按列 SalesYTD 对每个分区中的行进行排序。 SELECT 语句中的ORDER BY 按 TerritoryName 子句对整个查询结果集进行排序。

SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1), 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