SQL Server 排名函数实现
在SQL Server 中有四大排名函数分别是：
1、row_number()
2、ntile()
3、rank()
4、dense_rank()
-------------------------------------------------------------------------
为了方便演示我们先建立相关的表
create table t(ID int ,Name varchar(32));
go
insert into t(ID,Name) values(1,‘A‘),(2,‘A‘),(3,‘B‘),(4,‘C‘),(5,‘C‘),(6,‘D‘);
go
1、
row_number();
select ID,Name,ROW_NUMBER() over(order by Name) as rowNumber
from t;
go
select ID,Name,(select count(*) from t as b where b.ID<=a.ID) as rowNumber
from t as a;
go
2、
ntile()
select ID,Name,ntile(3) over(order by ID) nitleNumber from t;
go
declare @count as int =6;
declare @groupsize as int =3;
declare @tilesize as int =@count/@groupsize;
select ID,Name,((select count(*)+1 from t as b where a.ID>=b.ID)/@tilesize) ntileNumber
from t as a;
go
3、
rank()
select ID,Name,rank() over(order by Name)
from t;
go
select ID,Name,(select count(*)+1 from t as b where b.Name <a.Name)
from t as a;
go
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
