RANK (Transact-SQL)RANK (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

返回结果集的分区内每行的排名。Returns the rank of each row within the partition of a result set. 行的排名是相关行之前的排名数加一。The rank of a row is one plus the number of ranks that come before the row in question.

ROW_NUMBER 和 RANK 类似。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).

备注

RANK 是运行查询时计算出的临时值。RANK 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

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )  

参数Arguments

OVER ( [ partition_by_clause ] order_by_clause )OVER ( [ partition_by_clause ] order_by_clause)
partition_by_clause 将 FROM 子句生成的结果集划分为要应用函数的分区 。partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. 如果未指定,则此函数将查询结果集的所有行视为单个组。If not specified, the function treats all rows of the query result set as a single group. order_by_clause 在应用函数之前确定数据的顺序。order_by_clause determines the order of the data before the function is applied. 需要 order_by_clause 。The order_by_clause is required. 不能为 RANK 函数指定 OVER 子句的 <rows 或 range 子句/>。The <rows or range clause/> of the OVER clause cannot be specified for the RANK function. 有关详细信息,请参阅 OVER 子句 (Transact-SQL)For more information, see OVER Clause (Transact-SQL).

返回类型Return Types

bigintbigint

RemarksRemarks

如果两个或多个行与一个排名关联,则每个关联行将得到相同的排名。If two or more rows tie for a rank, each tied row receives the same rank. 例如,如果两位顶尖销售员具有相同的 SalesYTD 值,则他们将并列第一。For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. 由于已有两行排名在前,所以具有下一个最大 SalesYTD 的销售人员将排名第三。The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. 因此,RANK 函数并不总返回连续整数。Therefore, the RANK function does not always return consecutive integers.

用于整个查询的排序顺序决定了行在结果集中的显示顺序。The sort order that is used for the whole query determines the order in which the rows appear in a result set.

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

示例Examples

A.A. 对分区中的行进行排名Ranking rows within a partition

以下示例按照数量对指定清单位置的清单中的产品进行了排名。The following example ranks the products in inventory the specified inventory locations according to their quantities. 结果集按 LocationID 分区并在逻辑上按 Quantity 排序。The result set is partitioned by LocationID and logically ordered by Quantity. 注意,产品 494 和 495 具有相同的数量。Notice that products 494 and 495 have the same quantity. 因为它们是关联的,所以两者均排名第一。Because they are tied, they are both ranked one.

USE AdventureWorks2012;  
GO  
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity  
    ,RANK() OVER   
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank  
FROM Production.ProductInventory AS i   
INNER JOIN Production.Product AS p   
    ON i.ProductID = p.ProductID  
WHERE i.LocationID BETWEEN 3 AND 4  
ORDER BY i.LocationID;  
GO  

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

  
ProductID   Name                   LocationID   Quantity Rank  
----------- ---------------------- ------------ -------- ----  
494         Paint - Silver         3            49       1  
495         Paint - Blue           3            49       1  
493         Paint - Red            3            41       3  
496         Paint - Yellow         3            30       4  
492         Paint - Black          3            17       5  
495         Paint - Blue           4            35       1  
496         Paint - Yellow         4            25       2  
493         Paint - Red            4            24       3  
492         Paint - Black          4            14       4  
494         Paint - Silver         4            12       5  
 (10 row(s) affected)  

B.B. 对结果集中的所有行排名Ranking all rows in a result set

下面的示例返回按薪金排名的前十名员工。The following example returns the top ten employees ranked by their salary. 因为未指定 PARTITION BY 子句,所以,RANK 函数应用于结果集中的所有行。Because a PARTITION BY clause was not specified, the RANK function was applied to all rows in the result set.

USE AdventureWorks2012  
SELECT TOP(10) BusinessEntityID, Rate,   
       RANK() OVER (ORDER BY Rate DESC) AS RankBySalary  
FROM HumanResources.EmployeePayHistory AS eph1  
WHERE RateChangeDate = (SELECT MAX(RateChangeDate)   
                        FROM HumanResources.EmployeePayHistory AS eph2  
                        WHERE eph1.BusinessEntityID = eph2.BusinessEntityID)  
ORDER BY BusinessEntityID;  

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

BusinessEntityID Rate                  RankBySalary  
---------------- --------------------- --------------------  
1                125.50                1  
2                63.4615               4  
3                43.2692               8  
4                29.8462               19  
5                32.6923               16  
6                32.6923               16  
7                50.4808               6  
8                40.8654               10  
9                40.8654               10  
10               42.4808               9  

示例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 并行数据仓库Parallel Data Warehouse

C:对分区中的行进行排名C: Ranking rows within a partition

下面的示例根据销售代表的销售总额将每个销售区域中的销售代表进行排名。The following example ranks the sales representatives in each sales territory according to their total sales. 行集按 SalesTerritoryGroup 分区,按 SalesAmountQuota 排序。The rowset is partitioned by SalesTerritoryGroup and sorted by SalesAmountQuota.

-- Uses AdventureWorks  
  
SELECT LastName, SUM(SalesAmountQuota) AS TotalSales, SalesTerritoryRegion,  
    RANK() OVER (PARTITION BY SalesTerritoryRegion ORDER BY SUM(SalesAmountQuota) DESC ) AS RankResult  
FROM dbo.DimEmployee AS e  
INNER JOIN dbo.FactSalesQuota AS sq ON e.EmployeeKey = sq.EmployeeKey  
INNER JOIN dbo.DimSalesTerritory AS st ON e.SalesTerritoryKey = st.SalesTerritoryKey  
WHERE SalesPersonFlag = 1 AND SalesTerritoryRegion != N'NA'  
GROUP BY LastName, SalesTerritoryRegion;  

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

LastName          TotalSales     SalesTerritoryRegion  RankResult
----------------  -------------  -------------------  --------
Tsoflias          1687000.0000   Australia            1
Saraiva           7098000.0000   Canada               1
Vargas            4365000.0000   Canada               2
Carson            12198000.0000  Central              1
Varkey Chudukatil 5557000.0000   France               1
Valdez            2287000.0000   Germany              1
Blythe            11162000.0000  Northeast            1
Campbell          4025000.0000   Northwest            1
Ansman-Wolfe      3551000.0000   Northwest            2
Mensa-Annan       2753000.0000   Northwest            3
Reiter            8541000.0000   Southeast            1
Mitchell          11786000.0000  Southwest            1
Ito               7804000.0000   Southwest            2
Pak               10514000.0000  United Kingdom       1

另请参阅See Also

DENSE_RANK (Transact-SQL) DENSE_RANK (Transact-SQL)
ROW_NUMBER (Transact-SQL) ROW_NUMBER (Transact-SQL)
NTILE (Transact-SQL) NTILE (Transact-SQL)
排名函数 (Transact-SQL) Ranking Functions (Transact-SQL)
内置函数 (Transact-SQL)Built-in Functions (Transact-SQL)