排名函数 (Transact-SQL)Ranking Functions (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

排名函数为分区中的每一行返回一个排名值。Ranking functions return a ranking value for each row in a partition. 根据所用函数的不同,某些行可能与其他行接收到相同的值。Depending on the function that is used, some rows might receive the same value as other rows. 排名函数具有不确定性。Ranking functions are nondeterministic.

Transact-SQLTransact-SQL 提供下列排名函数:provides the following ranking functions:

RANKRANK NTILENTILE
DENSE_RANKDENSE_RANK ROW_NUMBERROW_NUMBER

示例Examples

以下示例显示了用在同一查询中的四个排名函数。The following example shows the four ranking functions used in the same query. 有关函数特定的示例,请参阅每个排名函数。For function-specific examples, see each ranking function.

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

另请参阅See Also

内置函数 (Transact-SQL) Built-in Functions (Transact-SQL)
OVER 子句 (Transact-SQL)OVER Clause (Transact-SQL)