SQL SERVER排名函数RANK，DENSE_RANK，NTILE，ROW_NUMBER

rrt44

SQL SERVER排名函数RANK，DENSE_RANK，NTILE，ROW_NUMBER
前言

本文意于用实例数据帮助理解SQL SERVER排名函数RANK，DENSE_RANK，NTILE，ROW_NUMBER。


准备工作

创建测试表：

[sql] view plaincopy
create table test(  
id int identity(1,1) primary key,  
testid int,  
name varchar(100)  
)  


插入测试数据：
[sql] view plaincopy
insert into test(testid,name)select 1,'LeeWhoee University'  
insert into test(testid,name)select 1,'LeeWhoee University'  
insert into test(testid,name)select 1,'LeeWhoee University'  
insert into test(testid,name)select 2,'LeeWhoee University'  
insert into test(testid,name)select 3,'排名函数'  
insert into test(testid,name)select 4,'排名函数'  
insert into test(testid,name)select 4,'排名函数'  


用一个SQL语句来查看各个函数的使用：
[sql] view plaincopy
select id,testid,  
ROW_NUMBER() over( order by testid) as rownum,  
RANK() over(order by testid) as ranknum,  
DENSE_RANK() over(order by testid) as denseranknum,  
Ntile(4) over ( order by testid) as ntilenum  
from test  
order by testid  


下面是运行结果：

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语句来查看各个函数的使用：
[sql] view plaincopy
select id,testid,name,  
ROW_NUMBER() over(partition by name order by testid) as rownum,  
RANK() over(partition by name order by testid) as ranknum,  
DENSE_RANK() over(partition by name order by testid) as denseranknum,  
Ntile(2) over (partition by name order by testid) as ntilenum  
from test  
order by name  


运行结果：

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()计算。


Ntile(2) over (partition by name order by testid)
按name先进行数据分区，然后对每一区进行正常的Ntile()计算。

为了便于区分数据，在这里进行了NTILE(2)而不是NTILE(4)。