SQL2005中row_number()等函数的用法关于row_number()、rank()、dense_rank()、ntile()的用法,下面以实例分别简单讲解一下。 create table gg(sname varchar(10),sort varchar(10),num int) go
insert into gg select '白芍','根茎类',55 union all select '法半夏','根茎类',78 union all select '柴胡','根茎类',60 union all select '川芎','根茎类',99 union all select '天香炉','草类',68 union all select '灯心草','草类',55 union all select '龙葵','草类',60 union all select '石见穿','草类',60 union all select '猪笼草','草类',70 union all select '益母草','草类',86 union all select '扁豆','果实类',86 union all select '草果','果实类',70 union all select '金樱子','果实类',55 union all select '女贞子','果实类',94 union all select '胖大海','果实类',66 union all select '桑葚','果实类',78
select sname,sort,num, row_number() over(order by num) as rownum, rank() over(order by num) as ranknum, dense_rank() over(order by num) as dersenum, ntile(3) over(order by num) as ntilenum from gg
--结果 --ROW_NUMBER()是按num由小到大逐一排名,不并列,排名连续 --RANK()是按num由小到大逐一排名,并列,排名不连续 --DENSE_RANK()是按num由小到大逐一排名,并列,排名连续 --NTILE()是按num由小到大分成组逐一排名,并列,排名连续
sname sort num rownum ranknum dersenum ntilenum (16 行受影响)
select sname,sort,num, row_number() over(partition by sort order by num) as rownum, rank() over(partition by sort order by num) as ranknum, dense_rank() over(partition by sort order by num) as dersenum, ntile(3) over(partition by sort order by num) as ntilenum from gg
--结果 此时加了partition by sort,就以类别来分类了,ntile(3)意思就是强制分为三组。
sname sort num rownum ranknum dersenum ntilenum (16 行受影响)
下面分别用SQL 2000实现,相对比2005要麻烦的多了。 --ROW_NUMBER在sql 2000中的实现 --利用临时表和IDENTITY(函数) select sname,num,identity(int,1,1) as rownumber into #tem from gg order by num
select sname,num,rownumber from #tem
drop table #tem go
--RANK在sql 2000中的实现 select sname,num, (select count(1)+1 from gg where num<g.num) as ranknum from gg g order by num go
--DENSE_RANK在sql 2000中的实现 select num,identity(int,1,1) as densenum into #t from gg group by num order by num
select r.sname,r.num,t.densenum from gg r join #t t on r.num=t.num order by num
drop table #t go |
|