首页 > 代码库 > Sql中Rank排名函数

Sql中Rank排名函数

A.对分区中的行进行排名

以下示例按照数量对指定清单位置的清单中的产品进行了排名。

 结果集按 LocationID 分区并在逻辑上按 Quantity 排序。

 注意,产品 494 和 495 具有相同的数量。 因为它们是关联的,所以两者均排名第一。

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

下面是结果集:

 
 
ProductID   Name                   LocationID   Quantity Rank----------- ---------------------- ------------ -------- ----494         Paint - Silver                3            49       1495         Paint - Blue                  3            49       1493         Paint - Red                   3            41       3496         Paint - Yellow              3            30       4492         Paint - Black                 3            17       5495         Paint - Blue                  4            35       1496         Paint - Yellow              4            25       2493         Paint - Red                   4            24       3492         Paint - Black                 4            14       4494         Paint - Silver                4            12       5 (10 row(s) affected)

B.对结果集中的所有行排名

下面的示例返回按薪金排名的前十名员工。 因为未指定 PARTITION BY 子句,所以,RANK 函数应用于结果集中的所有行。

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

下面是结果集:

 
 
BusinessEntityID Rate                  RankBySalary---------------- --------------------- --------------------1                125.50                12                63.4615               43                43.2692               84                29.8462               195                32.6923               166                32.6923               167                50.4808               68                40.8654               109                40.8654               1010               42.4808               9

Sql中Rank排名函数