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
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
