SQL排名函数比较

在SQL Server2005中有如下四个排名函数：

　　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

可以看出：

row_number 不论函数的值是什么 都一直顺序排序

rank 相对于row_number,对于相同的值会并列排名，然后跳过并列的序号继续排名

dense_rank 对于相同值会并列排名，然后按顺序继续排名，并不会跳过序号

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