2005比2000新增了幾個(gè)函數(shù)，分別是row_number()、rank()、dense_rank()、ntile()，下面以實(shí)例分別簡(jiǎn)單講解一下。

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 '石見(jiàn)穿','草類',60

union all

select '豬籠草','草類',70

union all

select '益母草','草類',86

union all

select '扁豆','果實(shí)類',86

union all

select '草果','果實(shí)類',70

union all

select '金櫻子','果實(shí)類',55

union all

select '女貞子','果實(shí)類',94

union all

select '胖大海','果實(shí)類',66

union all

select '桑葚','果實(shí)類',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

?

--結(jié)果

--ROW_NUMBER()是按num由小到大逐一排名，不并列，排名連續(xù)

--RANK()是按num由小到大逐一排名，并列，排名不連續(xù)

--DENSE_RANK()是按num由小到大逐一排名，并列，排名連續(xù)

--NTILE()是按num由小到大分成組逐一排名，并列，排名連續(xù)

?

sname????? sort?????? num?????? rownum??????? ranknum?????? dersenum?????? ntilenum
-------- --------- --------- ------------- ------------- --------------- ---------------
白芍???????? 根莖類?????? 55????????? 1????????? 1???????????? 1?????????????? 1
燈心草?????? 草類???????? 55????????? 2????????? 1???????????? 1?????????????? 1
金櫻子?????? 果實(shí)類?????? 55????????? 3????????? 1???????????? 1?????????????? 1
龍葵???????? 草類???????? 60????????? 4????????? 4???????????? 2?????????????? 1
石見(jiàn)穿?????? 草類???????? 60????????? 5????????? 4???????????? 2?????????????? 1
柴胡???????? 根莖類?????? 60????????? 6????????? 4???????????? 2?????????????? 1
胖大海?????? 果實(shí)類?????? 66????????? 7????????? 7???????????? 3?????????????? 2
天香爐?????? 草類???????? 68????????? 8????????? 8???????????? 4?????????????? 2
草果???????? 果實(shí)類?????? 70????????? 9????????? 9???????????? 5?????????????? 2
豬籠草?????? 草類???????? 70????????? 10???????? 9???????????? 5?????????????? 2
法半夏?????? 根莖類?????? 78????????? 11???????? 11??????????? 6?????????????? 2
桑葚???????? 果實(shí)類?????? 78????????? 12???????? 11??????????? 6?????????????? 3
益母草?????? 草類???????? 86????????? 13???????? 13??????????? 7?????????????? 3
扁豆???????? 果實(shí)類?????? 86????????? 14???????? 13??????????? 7?????????????? 3
女貞子?????? 果實(shí)類?????? 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

?

--結(jié)果

此時(shí)加了partition by sort，就以類別來(lái)分類了，ntile(3)意思就是強(qiáng)制分為三組。

?

sname????? sort?????????? num?????? rownum??????? ranknum??????? dersenum?????? ntilenum
-------- ----------?? --------- ------------- --------------- ---------------- -----------
燈心草?????? 草類????????? 55????????? 1??????????? 1?????????????? 1?????????????? 1
龍葵???????? 草類????????? 60????????? 2??????????? 2?????????????? 2?????????????? 1
石見(jiàn)穿?????? 草類????????? 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
金櫻子?????? 果實(shí)類??????? 55????????? 1??????????? 1?????????????? 1?????????????? 1
胖大海?????? 果實(shí)類??????? 66????????? 2??????????? 2?????????????? 2?????????????? 1
草果???????? 果實(shí)類??????? 70????????? 3??????????? 3?????????????? 3?????????????? 2
桑葚???????? 果實(shí)類??????? 78????????? 4??????????? 4?????????????? 4?????????????? 2
扁豆???????? 果實(shí)類??????? 86????????? 5??????????? 5?????????????? 5?????????????? 3
女貞子?????? 果實(shí)類??????? 94????????? 6??????????? 6?????????????? 6?????????????? 3

(16 行受影響)

?

下面分別用SQL 2000實(shí)現(xiàn)，相對(duì)比2005要麻煩的多了。

--ROW_NUMBER在sql 2000中的實(shí)現(xiàn)

--利用臨時(shí)表和IDENTITY（函數(shù)）

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中的實(shí)現(xiàn)

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中的實(shí)現(xiàn)

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

轉(zhuǎn)載于:https://www.cnblogs.com/kivenhou/archive/2010/11/04/1869326.html

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎(jiǎng)勵(lì)來(lái)咯，堅(jiān)持創(chuàng)作打卡瓜分現(xiàn)金大獎(jiǎng)

總結(jié)

以上是生活随笔為你收集整理的SQL2005中row_number()等函数的用法的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。