sql server 2005中---排序问题（row_number，rank,dense_rank,ntile）

--创建tjpm表
if object_id('tjpm') is not null
drop table tjpm
create table tjpm
(
  id int,
  mc  varchar(20),
  fs  int
)
--向tjpm表插入数据
insert into tjpm
select 1,'小丽',15
union all
select 2,'小丽',20
union all
select 3,'小强',20
union all
select 4,'小张',10
union all
select 5,'小闯',15
union all
select 6,'无聊',17

select * from tjpm

 

--row_number()的用法
select row_number() over(order by fs),* from tjpm order by fs desc

 

--rank() 的用法
select rank() over(order by fs ),* from tjpm order by fs desc

 

 

--dense_rank() 的用法
select dense_rank() over(order by fs ),* from tjpm order by fs desc

--ntile() 中的2表示的是分为2桶。 fs 为表中字段名
select ntile(2) over(order by fs ),* from tjpm order by fs desc