sql2005排名函数

 --http://www.cnblogs.com/nokiaguy/archive/2009/02/05/1384860.html
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(10))
insert [tb]
select 'a' union all
select 'a' union all
select 'a' union all
select 'a' union all
select 'a' union all
select 'b' union all
select 'b' union all
select 'b' union all
select 'b' union all
select 'b' union all
select 'c' union all
select 'c' union all
select 'c' union all
select 'c' union all
select 'c' union all
select 'd' union all
select 'd' union all
select 'd' union all
select 'd' union all
select 'd'
GO

select name,rank() over( order by name) from tb--序号不连续，相同的值的序号相同，接下来的一个值不相同的序号为前面的行数+1
select name,rank() over( partition by name order by name) from tb--按name分区，同一个区的值都是相同的，所以序号也是相同的，下一个区重新编号

select name,dense_rank() over (order by name) from tb--序号连续，相同的值的序号相同，接下来的一个值不相同的序号为前面的序号+1
select name,dense_rank() over (partition by name order by name) from tb

select name,row_number() over(order by name) from tb--序号连续，不管值是否相同
select name,row_number() over(partition by name order by name) from tb--序号连续，不管值是否相同，每一个分区重新编号

select name,ntile(3) over(order by name) from tb
select name,ntile(3) over(partition by name order by name) from tb