前言
本文意于用執行個體資料幫助了解SQL SERVER排名函數RANK,DENSE_RANK,NTILE,ROW_NUMBER。
準備工作
建立測試表:
?
1 2 3 4 5 | |
插入測試資料:
6 7 | |
用一個SQL語句來檢視各個函數的使用:
|
下面是運作結果:
id testid rownum ranknum denseranknum ntilenum
1 1 1 1 1 1
2 1 2 1 1 1
3 1 3 1 1 2
4 2 4 4 2 2
5 3 5 5 3 3
6 4 6 6 4 3
7 4 7 6 4 4
ROW_NUMBER() over( order by testid)
按testid升序排列為每一個testid生成與之對應的一個序列數字,這些數字是從1開始由小到大的不間斷數字。每個序列數字是唯一的。
RANK() over(order by testid)
按testid升序排列為每一個testid生成與之對應的一個排名數字,這些數字是從1開始由小到大排序(可能間斷)。相同的testid生成的排名數字也相同,但是下一排名數字不是由之前的排名數字加1計算出的,而是排名總數即行數。
DENSE_RANK() over(order by testid)
按testid升序排列為每一個testid生成與之對應的一個排名數字,這些數字是從1開始由小到大排序的不間斷數字(可能重複)。相同的testid生成的排名數字也相同,但是下一排名數字是由之前的排名數字加1計算出,而不是排名總數或行數。
Ntile(4) over ( order by testid)
按testid升序排列并将所有testid平均分成4組(最後一組testid總數可能少于其它組),然後為每一個testid生成與之對應的一個所屬組編号。組編号是從1開始由小到大的不間斷數字。
partition by
下面看一個帶partition by的SQL語句來檢視各個函數的使用:
|
運作結果:
id testid name rownum ranknum denseranknum ntilenum
1 1 LeeWhoee University 1 1 1 1
2 1 LeeWhoee University 2 1 1 1
3 1 LeeWhoee University 3 1 1 2
4 2 LeeWhoee University 4 4 2 2
5 3 排名函數 1 1 1 1
6 4 排名函數 2 2 2 1
7 4 排名函數 3 2 2 2
ROW_NUMBER() over(partition by name order by testid)
按name先進行資料分區,然後對每一區進行正常的ROW_NUMBER()計算。
RANK() over(partition by name order by testid)
按name先進行資料分區,然後對每一區進行正常的RANK()計算。
DENSE_RANK() over(partition by name order by testid)
按name先進行資料分區,然後對每一區進行正常的DENSE_RANK()計算。