欢迎来到.net学习网

欢迎联系站长一起更新本网站!QQ:879621940

您当前所在位置:首页 » SQLServer教程 » 正文

热门阅读

SQL Server中排名函数row_number,rank,dense_rank,ntile详解

创建时间:2011年11月09日 22:14  阅读次数:(7644)
分享到:
从SQL SERVER2005开始,SQL SERVER新增了四个排名函数,分别如下:
1.row_number
2.rank
3.dense_rank
4.ntile

下面我们对这四个函数一一进行讲解

1.Row_Number函数
可能row_number函数大家比较熟悉一些,因为它的用途非常的广泛,我们经常在分页与排序中用到它,它的功能就是在每一行中生成一个连续的不重复的序号(是不是和自增量有些像^_^)。下面我们用个示例来理解它:

先创建一个表:
create table Salary
(
SM money
)

然后往其中插入数据:
insert into Salary1(SM) 
select 3500
union all
select 3500
union all
select 3200
union all
select 3000
union all
select 2000
union all
select 2000

再利用Row_Number生成不重复的序号:
select SM,row_number() over(order by SM desc) as ID from salary

得到结果:
ID SM
1 3500.00
2 3500.00
3 3200.00
4 3000.00
5 2000.00
6 2000.00

可以看到,结果中生成了一列不重复的序号ID列。
那么SQL语句中的over(order by SM desc)是什么呢?这里是指定生成序号的依据,要生成不重复的序号,那么那行的序号最大?那么的序号最小?就是由over条件来指定的。order by SM desc就是告诉SQL,应该按照SM列中值从大到小的顺序生成ID列,实际上,row_number函数的原理就是先用over子句中的排序条件对记录进行排序,然后再按着这个顺序生成序号。如果SM中有重复的值,我们还可以使用多列来指定依据,比如order by SM1,SM2 Desc。如果没有其它列,那么SQL会依据自己的内部规则来对重复的行进行排序。

注意:over条件中的order by 与 SQL中排序条件order by不冲突。over中的order by仅是生成序号的排序条件,如果我们在sql后面使用order by来指定输出结果的排序条件,那么结果还是会按我们指定的输出,比如:
select row_number() over(order by SM desc) as id,SM from salary order by sm asc

得到结果:
id SM
5 2000.00
6 2000.00
4 3000.00
3 3200.00
1 3500.00
2 3500.00

2.Rank函数
该函数返回每行数据在结果集中的排名,如果有重复的排名,那么下一个排名与上一个排名就会断开,也就是不连续。这有点像我们读书时的成绩排名,实际上,它也在成绩,绩效等排名上用的非常多。
示例:
select rank() over(order by SM desc) as id,SM from salary

返回结果:
id SM
1 3500.00
1 3500.00
3 3200.00
4 3000.00
5 2000.00
5 2000.00

over条件与Row_Number函数一样。

3.dense_rank函数
我们使用Rank函数得到了不连续的排名,那么如果我们需要连续的排名的时候怎么办呢?呵呵,聪明的你应该已经猜到了,就是使用dense_rank函数,dense_rank函数的用法与Rank函数是一样的,只是它返回每行数据在结果集中连续的排名.
示例:
select Dense_rank() over(order by SM desc) as id,SM from salary

返回结果:
id SM
1 3500.00
1 3500.00
2 3200.00
3 3000.00
4 2000.00
4 2000.00

4.ntile函数
ntile函数可以将结果集放到我们指定数目的组中。比如我们的测试表salary中有6行数据,现在我们要将这6行数据按照salary值的从大到小的顺序分成4组,那么我们就可以用ntile函数。
示例:
select ntile(4) over(order by SM desc) as id, SM from salary

返回结果:
id SM
1 3500.00
1 3500.00
2 3200.00
2 3000.00
3 2000.00
4 2000.00

可以看到,salary的结果被分成4组了,id中的值,就是每行数据所在的组号(在msdn教程中把"组"称之为"桶",但我认为,叫成"组"更有利于我们理解)。

但是,nitle是根据什么依据来分组我们的数据呢?上面的结果中,为什么前两组中有两条数据,而后两组中只有一条数据呢?这就要了解分组的两个依据:
1,每组的记录数不能大于它上一组的记录数
2,所有组中的记录要么都相同,要么从某组开始后面所有组的记录数都与该组的记录数相同。这个理解比较抽象了,大家可以在实例中慢慢理解

到此,SQL SERVER中的排名函数我们就一一讲解完了,如果有不对或遗漏的部分,欢迎大家指出。
来源:.net学习网
说明:所有来源为 .net学习网的文章均为原创,如有转载,请在转载处标注本页地址,谢谢!
【编辑:Wyf

打赏

取消

感谢您的支持,我会做的更好!

扫码支持
扫码打赏,您说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

最新评论

共有评论0条
  • 暂无任何评论,请留下您对本文章的看法,共同参入讨论!
发表评论:
留言人:
内  容:
请输入问题 38+59=? 的结果(结果是:97)
结  果: