DENSE_RANK (Transact-SQL)DENSE_RANK (Transact-SQL)

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

此函数返回结果集分区中每行的排名,排名值没有间断。This function returns the rank of each row within a result set partition, with no gaps in the ranking values. 特定行的排名等于该特定行之前不同排名值的数量加一。The rank of a specific row is one plus the number of distinct rank values that come before that specific row.

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

语法Syntax

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )  

参数Arguments

<partition_by_clause><partition_by_clause>
首先将 FROM 子句生成的结果集划分到分区,然后将 DENSE_RANK 函数应用到每个分区。First divides the result set produced by the FROM clause into partitions, and then the DENSE_RANK function is applied to each partition. 有关 PARTITION BY 语法,请参阅 OVER 子句 (Transact-SQL)See OVER Clause (Transact-SQL) for the PARTITION BY syntax.

<order_by_clause><order_by_clause>
确定将 DENSE_RANK 函数应用于分区中的行时所基于的顺序。Determines the order in which the DENSE_RANK function applies to the rows in a partition.

返回类型Return Types

bigintbigint

RemarksRemarks

如果两个或更多行在同一分区中具有相同的排名值,那么每个行将获得相同的排名。If two or more rows have the same rank value in the same partition, each of those rows will receive the same rank. 例如,如果两位顶尖销售员具有相同的 SalesYTD 值,则他们的排名值都为一。For example, if the two top salespeople have the same SalesYTD value, they will both have a rank value of one. 接下来 SalesYTD 最高的销售人员排名值为二。The salesperson with the next highest SalesYTD will have a rank value of two. 这比所讨论的行之前的不同行的数量多了一。This exceeds the number of distinct rows that come before the row in question by one. 因此,DENSE_RANK 函数返回的数字没有间断,并且始终具有连续的排名值。Therefore, the numbers returned by the DENSE_RANK function do not have gaps, and always have consecutive rank values.

整个查询所用的排序顺序确定了各行在结果集中的顺序。The sort order used for the whole query determines the order of the rows in the result set. 这说明排名第一的行可以不是分区中的第一行。This implies that a row ranked number one does not have to be the first row in the partition.

DENSE_RANK 具有不确定性。DENSE_RANK is nondeterministic. 请参阅确定性函数和不确定性函数获取详细信息。See Deterministic and Nondeterministic Functions for more information.

示例Examples

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

此示例根据数量按指定库存位置对清单中的产品进行了排名。This example ranks the products in inventory, by the specified inventory locations, according to their quantities. DENSE_RANKLocationID 对结果集进行分区,并按 Quantity 对其进行逻辑排序。DENSE_RANK partitions the result set by LocationID and logically orders the result set by Quantity. 注意,产品 494 和 495 具有相同的数量。Notice that products 494 and 495 have the same quantity. 因为它们都具有相同的数量值,所以排名值都为一。Because they both have the same quantity value, they both have a rank value of one.

USE AdventureWorks2012;  
GO  
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity  
    ,DENSE_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       2  
496         Paint - Yellow                     3          30       3  
492         Paint - Black                      3          17       4  
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

此示例返回按薪资排前十名的员工。This example returns the top ten employees ranked by their salary. 由于 SELECT 语句未指定 PARTITION BY 子句,因此 DENSE_RANK 函数应用于所有结果集行。Because the SELECT statement did not specify a PARTITION BY clause, the DENSE_RANK function applied to all result set rows.

USE AdventureWorks2012;  
GO  
SELECT TOP(10) BusinessEntityID, Rate,   
       DENSE_RANK() OVER (ORDER BY Rate DESC) AS RankBySalary  
FROM HumanResources.EmployeePayHistory;  

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

BusinessEntityID Rate                  RankBySalary  
---------------- --------------------- --------------------  
1                125.50                1  
25               84.1346               2  
273              72.1154               3  
2                63.4615               4  
234              60.0962               5  
263              50.4808               6  
7                50.4808               6  
234              48.5577               7  
285              48.101                8  
274              48.101                8  

C.C. 用在同一查询中的四个排名函数Four ranking functions used in the same query

此示例显示四个排名函数This example shows the four ranking functions

用于相同查询中。used in the same query. 有关每个函数的具体示例,请参阅每个排名函数。See each ranking function for function-specific examples.

USE AdventureWorks2012;  
GO  
SELECT p.FirstName, p.LastName  
    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"  
    ,RANK() OVER (ORDER BY a.PostalCode) AS Rank  
    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"  
    ,NTILE(4) OVER (ORDER BY a.PostalCode) AS Quartile  
    ,s.SalesYTD  
    ,a.PostalCode  
FROM Sales.SalesPerson AS s   
    INNER JOIN Person.Person AS p   
        ON s.BusinessEntityID = p.BusinessEntityID  
    INNER JOIN Person.Address AS a   
        ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;  

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

FirstNameFirstName LastNameLastName Row NumberRow Number RankRank Dense RankDense Rank QuartileQuartile SalesYTDSalesYTD PostalCodePostalCode
MichaelMichael BlytheBlythe 11 11 11 11 4557045.04594557045.0459 9802798027
LindaLinda MitchellMitchell 22 11 11 11 5200475.23135200475.2313 9802798027
JillianJillian CarsonCarson 33 11 11 11 3857163.63323857163.6332 9802798027
GarrettGarrett VargasVargas 44 11 11 11 1764938.98591764938.9859 9802798027
TsviTsvi ReiterReiter 55 11 11 22 2811012.71512811012.7151 9802798027
ShuShu ItoIto 66 66 22 22 3018725.48583018725.4858 9805598055
JoséJosé SaraivaSaraiva 77 66 22 22 3189356.24653189356.2465 9805598055
DavidDavid CampbellCampbell 88 66 22 33 3587378.42573587378.4257 9805598055
TeteTete Mensa-AnnanMensa-Annan 99 66 22 33 1931620.18351931620.1835 9805598055
LynnLynn TsofliasTsoflias 1010 66 22 33 1758385.9261758385.926 9805598055
RachelRachel ValdezValdez 1111 66 22 44 2241204.04242241204.0424 9805598055
JaeJae PakPak 1212 66 22 44 5015682.37525015682.3752 9805598055
RanjitRanjit Varkey ChudukatilVarkey Chudukatil 1313 66 22 44 3827950.2383827950.238 9805598055

示例:Azure SQL 数据仓库Azure SQL Data Warehouse并行数据仓库Parallel Data WarehouseExamples: Azure SQL 数据仓库Azure SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse

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

此示例根据销售代表的销售总额将每个销售区域中的销售代表进行排名。This example ranks the sales representatives in each sales territory according to their total sales. DENSE_RANKSalesTerritoryGroup 对行集进行分区,并按 SalesAmountQuota 对结果集进行排序。DENSE_RANK partitions the rowset by SalesTerritoryGroup, and sorts the result set by SalesAmountQuota.

-- Uses AdventureWorks  
  
SELECT LastName, SUM(SalesAmountQuota) AS TotalSales, SalesTerritoryGroup,  
    DENSE_RANK() OVER (PARTITION BY SalesTerritoryGroup 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 SalesTerritoryGroup != N'NA'  
GROUP BY LastName, SalesTerritoryGroup;  

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

 LastName          TotalSales     SalesTerritoryGroup  RankResult  
----------------  -------------  -------------------  --------  
Pak               10514000.0000  Europe               1  
Varkey Chudukatil  5557000.0000  Europe               2  
Valdez             2287000.0000  Europe               3  
Carson            12198000.0000  North America        1  
Mitchell          11786000.0000  North America        2  
Blythe            11162000.0000  North America        3  
Reiter             8541000.0000  North America        4  
Ito                7804000.0000  North America        5  
Saraiva            7098000.0000  North America        6  
Vargas             4365000.0000  North America        7  
Campbell           4025000.0000  North America        8  
Ansman-Wolfe       3551000.0000  North America        9  
Mensa-Annan        2753000.0000  North America        10  
Tsoflias           1687000.0000  Pacific              1 

另请参阅See Also

RANK (Transact-SQL) RANK (Transact-SQL)
ROW_NUMBER (Transact-SQL) ROW_NUMBER (Transact-SQL)
NTILE (Transact-SQL) NTILE (Transact-SQL)
排名函数 (Transact-SQL) Ranking Functions (Transact-SQL)
函数Functions