2005比2000新增了几个函数，分别是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-------- --------- --------- ------------- ------------- --------------- ---------------白芍 根茎类 55 1 1 1 1灯心草 草类 55 2 1 1 1金樱子 果实类 55 3 1 1 1龙葵 草类 60 4 4 2 1石见穿 草类 60 5 4 2 1柴胡 根茎类 60 6 4 2 1胖大海 果实类 66 7 7 3 2天香炉 草类 68 8 8 4 2草果 果实类 70 9 9 5 2猪笼草 草类 70 10 9 5 2法半夏 根茎类 78 11 11 6 2桑葚 果实类 78 12 11 6 3益母草 草类 86 13 13 7 3扁豆 果实类 86 14 13 7 3女贞子 果实类 94 15 15 8 3川芎 根茎类 99 16 16 9 3
(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-------- ---------- --------- ------------- --------------- ---------------- -----------灯心草 草类 55 1 1 1 1龙葵 草类 60 2 2 2 1石见穿 草类 60 3 2 2 2天香炉 草类 68 4 4 3 2猪笼草 草类 70 5 5 4 3益母草 草类 86 6 6 5 3白芍 根茎类 55 1 1 1 1柴胡 根茎类 60 2 2 2 1法半夏 根茎类 78 3 3 3 2川芎 根茎类 99 4 4 4 3金樱子 果实类 55 1 1 1 1胖大海 果实类 66 2 2 2 1草果 果实类 70 3 3 3 2桑葚 果实类 78 4 4 4 2扁豆 果实类 86 5 5 5 3女贞子 果实类 94 6 6 6 3
(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
