排名函数是Sql Server2005新增的功能,下面简单介绍一下他们各自的用法和区别。我们新建一张Order表并添加一些初始数据方便我们查看效果。
一、ROW_NUMBER
语法格式:
select ROW_NUMBER() OVER( order by [SubTime] desc ) as row_num,* from [Order]
row_number的用途的非常广泛,排序最好用他,一般可以用来实现web程序的分页,他会为查询出来的每一行记录生成一个序号,依次排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号
其基本原理是先使用over子句中的排序语句对记录进行排序,然后按照这个顺序生成序号。over子句中的order by子句与SQL语句中的order by子句没有任何关系,这两处的order by 可以完全不同
注意:在使用row_number实现分页时需要特别注意一点,over子句中的order by 要与Sql排序记录中的order by 保持一致,否则得到的序号可能不是连续的。
二、RANK
select RANK() OVER(order by [UserId]) as rank,* from [Order]
三、DENSE_RANK
select DENSE_RANK() OVER(order by [UserId]) as den_rank,* from [Order]
四、NTILE
ntile函数可以对序号进行分组处理,将有序分区中的行分发到指定数目的组中。 各个组有编号,编号从一开始。 对于每一个行,ntile 将返回此行所属的组的编号。这就相当于将查询出来的记录集放到指定长度的数组中,每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引(从1开始)。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数,用来指定桶数。下面的SQL语句使用ntile函数对Order表进行了装桶处理:
select NTILE(4) OVER(order by [SubTime] desc) as ntile,* from [Order]