sql次序函數--四個

標籤：排序   sql   int   跳過   tab   數學   union   英語   strong   

1 row_number

2 rank

3 dense_rank

4 ntile

例子如下：

select * into #MyTable
from
(select ‘語文‘ as 課程,70 as 成績
union all
select ‘數學‘ ,80
union all
select ‘數學‘ ,80
union all
select ‘英語‘ ,90
union all
select ‘英語‘ ,100
union all
select ‘英語‘ ,70
) as tb

 

課程   成績
---- -----------
語文   70
數學   80
數學   80
英語   90
英語   100
英語   70

 

select 成績
,row_number()over( order by 成績 desc) as row_number
,rank() over( order by 成績 desc) as rank
,dense_rank()over( order by 成績 desc) as dense_rank
,ntile(4) over( order by 成績 desc) as ntile
from #MyTable

結果如下

成績          row_number           rank                 dense_rank           ntile
----------- -------------------- -------------------- -------------------- --------------------
100         1                    1                    1                    1
90          2                    2                    2                    1
80          3                    3                    3                    2
80          4                    3                    3                    2
70          5                    5                    4                    3
70          6                    5                    4                    4

分析：

1 row_number 　　不論函數的值是什麼都一直排序。

2 rank 　　對於相同的值會並列排名，然後跳過須後繼續排名。

3 dense_rank  對於相同的值會並列排名，然後會按序號繼續排名，不會跳過序號排名。

4 ntile 將資料分成若干塊，主要用於分析 ？

 

 當然還可以在 row_number()over()中加入partition by 在小組中分別排名：

 

select 課程,成績
,row_number()over( partition by 課程 order by 成績 desc) as row_number
,rank() over( partition by 課程 order by 成績 desc) as rank
,dense_rank()over( partition by 課程 order by 成績 desc) as dense_rank
,ntile(4) over( partition by 課程 order by 成績 desc) as ntile
from #MyTable

 

課程   成績          row_number           rank                 dense_rank           ntile
---- ----------- -------------------- -------------------- -------------------- --------------------
數學   80          1                    1                    1                    1
數學   80          2                    1                    1                    2
英語   100         1                    1                    1                    1
英語   90          2                    2                    2                    2
英語   70          3                    3                    3                    3
語文   70          1                    1                    1                    1

sql次序函數--四個