SQL2005中row_number()等函数的用法
2005比2000新增了几个函数，分别是row_number()、rank()、dense_rank()、ntile()，下面以实例分别简单讲解一下。
代码
1 create table gg(sname varchar(10),sort varchar(10),num int)
2 go
3
4 insert into gg
5 select ‘白芍‘,‘根茎类‘,55
6 union all
7 select ‘法半夏‘,‘根茎类‘,78
8 union all
9 select ‘柴胡‘,‘根茎类‘,60
10 union all
11 select ‘川芎‘,‘根茎类‘,99
12 union all
13 select ‘天香炉‘,‘草类‘,68
14 union all
15 select ‘灯心草‘,‘草类‘,55
16 union all
17 select ‘龙葵‘,‘草类‘,60
18 union all
19 select ‘石见穿‘,‘草类‘,60
20 union all
21 select ‘猪笼草‘,‘草类‘,70
22 union all
23 select ‘益母草‘,‘草类‘,86
24 union all
25 select ‘扁豆‘,‘果实类‘,86
26 union all
27 select ‘草果‘,‘果实类‘,70
28 union all
29 select ‘金樱子‘,‘果实类‘,55
30 union all
31 select ‘女贞子‘,‘果实类‘,94
32 union all
33 select ‘胖大海‘,‘果实类‘,66
34 union all
35 select ‘桑葚‘,‘果实类‘,78
36
37 select sname,sort,num,
38 row_number() over(order by num) as rownum,
39 rank() over(order by num) as ranknum,
40 dense_rank() over(order by num) as dersenum,
41 ntile(3) over(order by num) as ntilenum
42 from gg
43
--结果
--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（函数）
代码
1 select sname,num,identity(int,1,1) as rownumber
2 into #tem
3 from gg
4 order by num
5
6 select sname,num,rownumber
7 from #tem
8
9 drop table #tem
10 go
11
12 --RANK在sql 2000中的实现
13 select sname,num,
14 (select count(1)+1 from gg where num<g.num) as ranknum
15 from gg g
16 order by num
17 go
18
19 --DENSE_RANK在sql 2000中的实现
20 select num,identity(int,1,1) as densenum
21 into #t
22 from gg
23 group by num
24 order by num
25
26 select r.sname,r.num,t.densenum
27 from gg r join #t t
28 on r.num=t.num
29 order by num
30
31 drop table #t
32 go
33
row_number()、rank()、dense_rank()、ntile(),布布扣,bubuko.com
row_number()、rank()、dense_rank()、ntile()
原文：http://www.cnblogs.com/lonelyxmas/p/3620887.html
