Sql中Rank次序函數

標籤：des   style   color   os   io   ar   art   div   cti   

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次序函數