转：SqlServer四种排序：ROW_NUMBER()/RANK()/DENSE_RANK()/ntile() over()

原文转载自：http://blog.csdn.net/a5685263/article/details/52187070

 

以下为转载内容：

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

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'张三'

PS：最后一个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() over()还有一种用法，可以针对某列进行分组排序。

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

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

结果：

 

二、RANK()over(order by columnname)：
大家可以从下面的结果集看到，结果集少了5的编号，而有两个4的编号，然后直接跳到编号6。

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

结果：

  
三、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()函数最后的编号和分组的数目有关。

 

四、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行。
依此类推。