SQL中rownumber rank denserank ntile 函数Word格式文档下载.docx
《SQL中rownumber rank denserank ntile 函数Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《SQL中rownumber rank denserank ntile 函数Word格式文档下载.docx（10页珍藏版）》请在冰豆网上搜索。
,s.SalesYTD,a.PostalCodeFROMSales.SalesPersonsJOINPerson.Contactcons.SalesPersonID=c.ContactIDJOINPerson.AddressaONa.AddressID=c.ContactIDWHERETerritoryIDISNOTNULLANDSalesYTD<
>
0/*
FirstNameLastNameRowNumberSalesYTDPostalCode
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ShelleyDyck15200475.231398027
GailErickson25015682.375298055
MaciejDusza34557045.045998027
LindaEcoffey43857163.633298027
MarkErickson53827950.23898055
TerryEminhizer63587378.425798055
MichaelEmanuel73189356.246598055
JaunaElson83018725.485898055
CarolElliott92811012.715198027
JanethEsteves102241204.042498055
MarthaEspinoza111931620.183598055
CarlaEldridge121764938.985998027
TwannaEvans131758385.92698055
（13行受影响）*//*以下示例将返回行号为50到60（含）的行，并以OrderDate排序。
*/USEAdventureWorks;
GOWITHOrderedOrdersAS
（SELECTSalesOrderID,OrderDate,
ROW_NUMBER（）OVER（orderbyOrderDate）asRowNumberFROMSales.SalesOrderHeader）SELECT*FROMOrderedOrdersWHERERowNumberbetween50and60;
/*
SalesOrderIDOrderDateRowNumber
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
437082001-07-0300:
00:
00.00050
437092001-07-0300:
00.00051
437102001-07-0300:
00.00052
437112001-07-0400:
00.00053
437122001-07-0400:
00.00054
437132001-07-0500:
00.00055
437142001-07-0500:
00.00056
437152001-07-0500:
00.00057
437162001-07-0500:
00.00058
437172001-07-0500:
00.00059
437182001-07-0600:
00.00060
（11行受影响）*/------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RANK（）
返回结果集的分区内每行的排名。
行的排名是相关行之前的排名数加一。
RANK（）OVER（[<
partition_by_clause>
order_by_clause>
）
如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。
例如，如果两位顶尖销售员具有同样的SalesYTD值，他们将并列第一。
由于已有两行排名在前，所以具有下一个最大SalesYTD的销售人员将排名第三。
因此，RANK函数并不总返回连续整数。
用于整个查询的排序顺序决定了行在结果集中的显示顺序。
这也隐含了行在每个分区中的排名。
将FROM子句生成的结果集划分为要应用RANK函数的分区。
确定将RANK值应用于分区中的行时所基于的顺序。
bigint
/*以下示例按照数量对清单中的产品进行了排名。
行集按LocationID分区，按Quantity排序。
USEAdventureWorks;
GO
SELECTi.ProductID,p.Name,i.LocationID,i.Quantity,RANK（）OVER（PARTITIONBYi.LocationIDorderbyi.Quantity）asRANK
FROMProduction.ProductInventoryiJOINProduction.Productp
ONi.ProductID=p.ProductID
ORDERBYp.Name
ProductIDNameLocationIDQuantityRANK
---------------------------------------------------------------------------------------------------
1AdjustableRace632471
1AdjustableRace140878
1AdjustableRace50353117
2BearingBall631867
2BearingBall142785
2BearingBall50364122
3BBBallBearing50324106
3BBBallBearing1585110
3BBBallBearing6443115
4HeadsetBallBearings151299
4HeadsetBallBearings6422108
4HeadsetBallBearings50388140
316Blade1038833
......
（1069行受影响）*/--接上.
-------------------------------------------------------------------------------------DENSE_RANK（）
返回结果集分区中行的排名，在排名中没有任何间断。
行的排名等于所讨论行之前的所有排名数加一。
DENSE_RANK（）OVER（[<
）
如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。
例如，如果两位顶尖销售员具有相同的SalesYTD值，则他们将并列第一。
接下来SalesYTD最高的销售人员排名第二。
该排名等于该行之前的所有行数加一。
因此，DENSE_RANK函数返回的数字没有间断，并且始终具有连续的排名。
整个查询所用的排序顺序确定了各行在结果中的显示顺序。
这说明排名第一的行可以不是分区中的第一行。
将FROM子句所生成的结果集划分为数个将应用DENSE_RANK函数的分区。
确定将DENSE_RANK值应用于分区中各行的顺序。
/*以下示例返回各位置上产品数量的DENSE_RANK。
*/USEAdventureWorks;
GOSELECTi.ProductID,p.Name,i.LocationID,i.Quantity,DENSE_RANK（）OVER（PARTITIONBYi.LocationIDorderbyi.Quantity）asDENSE_RANKFROMProduction.ProductInventoryiJOINProduction.ProductpONi.ProductID=p.ProductIDORDERBYName;
GO/*
ProductIDNameLocationIDQuantityDENSE_RANK
1AdjustableRace140857
1AdjustableRace632452
1AdjustableRace5035382
879All-PurposeBikeStand714434
712AWCLogoCap728838
3BBBallBearing5032474
3BBBallBearing644381
3BBBallBearing158582*/
将上面三个函数放在一起计算，更能明显看出各个函数的功能。
CREATETABLErankorder（orderidINT,qtyINT）INSERTrankorderVALUES（30001,10）INSERTrankorderVALUES（10001,10）INSERTrankorderVALUES（10006,10）INSERTrankorderVALUES（40005,10）INSERTrankorderVALUES（30003,15）INSERTrankorderVALUES（30004,20）INSERTrankorderVALUES（20002,20）INSERTrankorderVALUES（20001,20）INSERTrankorderVALUES（10005,30）INSERTrankorderVALUES（30007,30）INSERTrankorderVALUES（40001,40）INSERTrankorderVALUES（30007,30）GO--对一个列qty进行的排序SELECTorderid,qty,
ROW_NUMBER（）OVER（ORDERBYqty）ASrownumber,
RANK（）OVER（ORDERBYqty）ASrank,
DENSE_RANK（）OVER（ORDERBYqty）ASdenserankFROMrankorderORDERBYqty/*
orderidqtyrownumberrankdenserank
----------------------------------------------------------------------------------
3000110111
1000110211
1000610311
4000510411
3000315552
3000420663
2000220763
2000120863
1000530994
30007301094
30007301194
400014012125
（12行受影响）*/--对两个列qty,orderid进行的排序SELECTorderid,qty,
ROW_NUMBER（）OVER（ORDERBYqty,orderid）ASrownumber,
RANK（）OVER（ORDERBYqty,orderid）ASrank,
DENSE_RANK（）OVER（ORDERBYqty,orderid）ASdenserankFROMrankorderORDERBYqty,orderiddroptablerankorder/*
1000110111
1000610222
3000110333
4000510444
3000315555
2000120666
2000220777
3000420888
1000530999
3000730101010
3000730111010
4000140121211
（12行受影响）*/
--示例数据CREATETABLEtb（Namevarchar（10）,Scoredecimal（10,2））INSERTtbSELECT'
aa'
99UNIONALLSELECT'
bb'
56UNIONALLSELECT'
cc'
dd'
77UNIONALLSELECT'
ee'
78UNIONALLSELECT'
ff'
76UNIONALLSELECT'
gg'
50GO--1.名次生成方式1,Score重复时合并名次SELECT*,Place=（SELECTCOUNT（DISTINCTScore）FROMtbWHEREScore>
=a.Score）FROMtbaORDERBYPlace/*--结果
NameScorePlace
--------------------------------------------
aa99.001
ee78.002
gg78.002
dd77.003
ff76.004
bb56.005
cc56.005
ff50.006
--*/
--2.名次生成方式2,Score重复时保留名次空缺SELECT*,Place=（SELECTCOUNT（Score）FROMtbWHEREScore>
a.Score）+1FROMtbaORDERBYPlace/*--结果
-------------------------------------------
dd77.004
ff76.005
bb56.006
cc56.006
ff50.008
createtablecj（bjint,zfint）insertintocjselect1,98insertintocjselect2,97insertintocjselect1,96insertintocjselect2,96insertintocjselect1,95insertintocjselect2,94insertintocjselect1,94insertintocjselect2,94insertintocjselect1,93selectbj,
zmc=（selectcount（distinctzf）fromcjwherezf>
a.zf）+1,
bmc=（selectcount（distinctzf）fromcjwherezf>
a.zfandbj=a.bj）+1,
zffromcjagodroptablecj/*
bjzmcbmczf
--------------------------------------------
11198
22197
13296
23296
14395
25394
15494
16593
（所影响的行数为9行）*/
SQLserver2005新增的几个函数,分别是row_number（）、rank（）、,DENSE_RANK（）、ntile（）下面以实例分别简单讲解。
1.row_number（）
先来点数据,先建个表
SETNOCOUNTON
CREATETABLEPerson（
FirstNameVARCHAR（10）,
AgeINT,
GenderCHAR
（1））
INSERTINTOPersonVALUES（'
Ted'
23,'
M'
John'
40,'
George'
6,'
Mary'
11,'
F'
Sam'
17,'
Doris'
Frank'
38,'
Larry'
5,'
Sue'
29,'
Sherry'
Marty'
直接用例子说明问题:
SELECTROW_NUMBER（）OVER（ORDERBYAge）AS[RowNumberbyAge],
FirstName,
Age
FROMPerson
出现的数据如下
RowNumberbyAgeFirstNameAge
--------------------------------------------
1Larry5
2Doris6
3George6
4Mary11
5Sherry11
6Sam17
7Ted23
8Marty23
9Sue29
10Frank38
11John40
可以观察到,是根据年龄升序排列了，并且row_number（）是给出了序列号了,这个序列号被重命名为RowNumberbyAge,
与sqlserver2000对比:
如果在sqlserver2000中实现相对麻烦一些，我们可以利用IDENTITY（）函数实现，但IDENTITY（）函数只能用在sqlserver2000临时表中，因此需要将数据检索到临时表里。
selectidentity（int,1,1）as[RowNumberbyAge],FirstName,Ageinto#AfromPersonorderbyAge
select*from#A
droptable#a
如果不想按年龄排序,可以这样写
SELECTROW_NUMBER（）OVER（ORDERBY（SELECT1））AS[RowNumberbyRecordSet],
升级会员 