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 的差異就在於
同名後，是不是要繼續數，還是跳過一個名次
大概就是這樣囉~ 這個滿常用的，所以紀錄一下