ROW_NUMBER() OVER函数的基本用法

最新推荐文章于 2020-10-18 14:04:39 发布

转载最新推荐文章于 2020-10-18 14:04:39 发布 · 811 阅读

文章标签：

#ROW_NUMBER OVER

SQL Server 专栏收录该内容

11 篇文章

订阅专栏

本文详细介绍了SQL中ROW_NUMBER()函数的用法及其实现案例，包括如何使用PARTITION BY和ORDER BY来实现复杂的数据分组和排序，通过示例展示了如何计算销售人员的行号、获取特定行的子集以及按列对结果集进行分区并排序。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

语法

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_clauseORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。它是必需的。有关详细信息，请参阅 OVER 子句 (Transact-SQL)。
返回类型：bigint

*不保证使用 ROW_NUMBER() 的查询返回的行对于每次执行在顺序上保持完全相同，除非以下条件成立。
分区列的值是唯一的。
ORDER BY 列的值是唯一的。
分区列和 ORDER BY 列的值的组合是唯一的。

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

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


B.返回行的子集
下面的示例按 OrderDate 的顺序计算 SalesOrderHeader 表中所有行的行号，并只返回行 50 到 60（含）。
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;

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

USE AdventureWorks2012;
GO
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