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

前言

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

准备工作

创建测试表：

 

?

1

2

3

4

5

createtable test(

idintidentity(1,1) primarykey,

testidint,

namevarchar(100)

) 

插入测试数据：

 

?

1

2

3

4

5

6

7

insertinto test(testid,name)select1,'LeeWhoee University'

insertinto test(testid,name)select1,'LeeWhoee University'

insertinto test(testid,name)select1,'LeeWhoee University'

insertinto test(testid,name)select2,'LeeWhoee University'

insertinto test(testid,name)select3,'排名函数'

insertinto test(testid,name)select4,'排名函数'

insertinto test(testid,name)select4,'排名函数'

用一个SQL语句来查看各个函数的使用：

 

?

1

2

3

4

5

6

7

selectid,testid,

ROW_NUMBER() over( orderby testid) asrownum,

RANK() over(orderby testid) asranknum,

DENSE_RANK() over(orderby testid) asdenseranknum,

Ntile(4) over ( orderby testid) asntilenum

fromtest

orderby 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语句来查看各个函数的使用：

 

?

1

2

3

4

5

6

7

selectid,testid,name,

ROW_NUMBER() over(partition byname order by testid) asrownum,

RANK() over(partition byname order by testid) asranknum,

DENSE_RANK() over(partition byname order by testid) asdenseranknum,

Ntile(2) over (partition byname order by testid) asntilenum

fromtest

orderby 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)。

来源：http://www.2cto.com/database/201403/282530.html