在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