几种排名函数的区别

ROW_NUMBER          DENSE_RANK           RANK                 NTILE

 

ROW_NUMBER          （返回结果集分区内行的序列号，每个分区的第一行从 1 开始）

语法： ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

示例： SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

 

DENSE_RANK           （返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一）

语法： DENSE_RANK( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

示例： USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity,
    DENSE_RANK() OVER (PARTITION BY i.LocationID ORDER BY i.Quantity)     AS DENSE_RANK
FROM Production.ProductInventory i
    INNER JOIN Production.Product p
        ON i.ProductID = p.ProductID
ORDER BY Name;
GO

 

RANK           （返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一）

语法： RANK  ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

示例： USE AdventureWorks;
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 i
    INNER JOIN Production.Product p
        ON i.ProductID = p.ProductID
ORDER BY p.Name;
GO

 

NTILE           （将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将返回此行所属的组的编号）

语法： NTILE (integer_expression)    OVER ( [ <partition_by_clause> ] < order_by_clause > )

示例： USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;
GO

 

 

 

 

以下示例显示了用在同一查询中的四个排名函数。有关每个函数的具体示例，请参阅每个排名函数。

USE AdventureWorks;
GO
SELECT c.FirstName, c.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 s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;

 

效果如下：