排名函数:RANK、DENSE_RANK、NTILE、ROW_NUMBER值得一用 (TransactSQL)

排名函数为分区中的每一行返回一个排名值。根据所用函数的不同，某些行可能与其他行接收到相同的值。排名函数具有不确定性。

Transact-SQL 提供下列排名函数：都需要第二个参数OVER ( [ <partition_by_clause> ] < order_by_clause > )

RANK	NTILE
DENSE_RANK	ROW_NUMBER

示例

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


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;

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;

下面是结果集：

FirstName	LastName	Row Number	Rank	Dense Rank	Quartile	SalesYTD	PostalCode
Maciej	Dusza	1	1	1	1	4557045	98027
Shelley	Dyck	2	1	1	1	5200475	98027
Linda	Ecoffey	3	1	1	1	3857164	98027
Carla	Eldridge	4	1	1	1	1764939	98027
Carol	Elliott	5	1	1	2	2811013	98027
Jauna	Elson	6	6	2	2	3018725	98055
Michael	Emanuel	7	6	2	2	3189356	98055
Terry	Eminhizer	8	6	2	3	3587378	98055
Gail	Erickson	9	6	2	3	5015682	98055
Mark	Erickson	10	6	2	3	3827950	98055
Martha	Espinoza	11	6	2	4	1931620	98055
Janeth	Esteves	12	6	2	4	2241204	98055
Twanna	Evans	13	6	2	4	1758386	98055