ROW_NUMBER()/RANK()/DENSE_RANK()/ntile() over()

时间:2021-03-20 18:05:12

ROW_NUMBER()/RANK()/DENSE_RANK()/ntile() over()

 

今天女票问我SqlServer的四种排序,当场写了几句Sql让她了解,现把相关Sql放上来。

首先,我们创建一些测试数据。

ROW_NUMBER()/RANK()/DENSE_RANK()/ntile() over()
if OBJECT_ID('Tempdb.dbo.#Tmp') is not null
drop table #Tmp
create table #Tmp
(
name nvarchar(10)
) insert into #Tmp
select N'张三'
union
select N'李四'
union
select N'王五'
union
select N'赵六'
union
select N'朱七'
union
select N'王八'
union all
select N'张三'
ROW_NUMBER()/RANK()/DENSE_RANK()/ntile() over()

最后一个union用union all,因为我们多一行"张三"。

一、ROW_NUMBER() over(partition by columnname order by columnname)

select ROW_NUMBER()over(order by name) as num,* from #Tmp

可以得到按name排序的结果集。

ROW_NUMBER()/RANK()/DENSE_RANK()/ntile() over()

ROW_NUMBER() over()还有一种用法,可以针对某列进行分组排序。

下面结果可以看到张三有1和2两个排序,而其他的名字排序都只有1。

select ROW_NUMBER()over(partition by name order by name) as num,* from #Tmp

ROW_NUMBER()/RANK()/DENSE_RANK()/ntile() over()

二、RANK()over(order by columnname)

大家可以从下面的结果集看到,结果集少了5的编号,而有两个4的编号,然后直接跳到编号6。

select RANK()over(order by name),* from #Tmp

ROW_NUMBER()/RANK()/DENSE_RANK()/ntile() over()

三、DENSE_RANK()over(order by columnname)

select DENSE_RANK()over(order by name),* from #Tmp

执行Sql后发现,下面的结果集有2个编号4的行,紧接着就是编号5的行。

DENSE_RANK()函数和RANK()函数差不多。

RANK()函数不管分几组,最后的编号一定和行数相同。

DENSE_RANK()函数最后的编号和分组的数目有关。

ROW_NUMBER()/RANK()/DENSE_RANK()/ntile() over()

四、NTILE()OVER(ORDER BY COLUMNNAME)

select NTILE(2)over(order by name),* from #Tmp
select NTILE(3)over(order by name),* from #Tmp
NTILE后面的数字,是要把查询得到的结果平均分为几组。
如下图分为2和3组。
如果行数平均划分后还有余行,那么就把行分在最前面的几组上。
比如我们的结果有7行,要分为3组。
那么第一组3行,第二组2行,第三组2行。
如果我们结果有14行,平均分为3组。
那么第一组5行,第二组5行,第三组4行。
依此类推。

ROW_NUMBER()/RANK()/DENSE_RANK()/ntile() over()