[SQL] Rank、Dense_Rank、Row_Number用法與範例

Rank() over (Partition by 欄位 Order by 欄位 )

DENSE_RANK() over (Partition by 欄位 Order by 欄位 )

ROW_NUMBER() over (Partition by 欄位 Order by 欄位 )

先說明Partition 的用意,其實就是分隔

 

    select RANK()Over (Partition by SO.ShipCountry order by COUNT(SO.EmployeeID) desc) as seq ,

    SO.ShipCountry,

    SO.EmployeeID,

    HE.FirstName,

    HE.LastName,

    COUNT(*) as cnt

    from Sales.Orders SO

    inner join HR.Employees HE

        on SO.EmployeeID = HE.EmployeeID

    group by ShipCountry,SO.EmployeeID,HE.FirstName,HE.LastName

 

以這欄位來看

 


Partition 的用意就是讓計數,重新計算

而排列的依據就是Order by

我用下方例子讓這三個函式更明顯一點

 

SELECT  ProductID ,

        ProductName ,

        UnitPrice ,

        RANK() OVER ( ORDER BY UnitPrice DESC ) AS PriceRank ,

        DENSE_RANK() OVER ( ORDER BY UnitPrice DESC ) AS PriceDenseRank ,

        ROW_NUMBER() OVER ( ORDER BY UnitPrice DESC ) AS SN

FROM    Production.Products

ORDER BY SN

 

 

Row_Number 就是流水號,很容易明白

而Rank 與 Dense_Rank 的差異就在於

同名後,是不是要繼續數,還是跳過一個名次

大概就是這樣囉~ 這個滿常用的,所以紀錄一下