row_number()、rank()、dense_rank()、ntile()

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

转载于:https://www.cnblogs.com/weiqt/articles/1848536.html