ROWNUMBERRANKDENSERANKLAG.docx
《ROWNUMBERRANKDENSERANKLAG.docx》由会员分享，可在线阅读，更多相关《ROWNUMBERRANKDENSERANKLAG.docx（14页珍藏版）》请在冰豆网上搜索。
ROWNUMBERRANKDENSERANKLAG
ROW_NUMBER、（DENSE_）RANK、LAG的用法
SQLServer2005引入几个新的排序（排名）函数,如ROW_NUMBER、RANK、DENSE_RANK等。
这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。
--------------------------------------------------------------------------
ROW_NUMBER（）
说明：
返回结果集分区内行的序列号，每个分区的第一行从1开始。
语法：
ROW_NUMBER（）OVER（[]）。
备注：
ORDERBY子句可确定在特定分区中为行分配唯一ROW_NUMBER的顺序。
参数：
：
将FROM子句生成的结果集划入应用了ROW_NUMBER函数的分区。
：
确定将ROW_NUMBER值分配给分区中的行的顺序。
返回类型：
bigint。
示例：
/*以下示例将根据年初至今的销售额，返回AdventureWorks中销售人员的ROW_NUMBER。
*/
USEAdventureWorks
GO
SELECTc.FirstName,c.LastName,ROW_NUMBER（）OVER（ORDERBYSalesYTDDESC）AS'RowNumber',s.SalesYTD,a.PostalCode
FROMSales.SalesPersonsJOINPerson.Contactcons.SalesPersonID=c.ContactID
JOINPerson.AddressaONa.AddressID=c.ContactID
WHERETerritoryIDISNOTNULLANDSalesYTD<>0
/*
FirstName LastName RowNumber SalesYTD PostalCode
--------- ---------- ---------- ------------ ----------------------------
Shelley Dyck 1 5200475.2313 98027
Gail Erickson 2 5015682.3752 98055
Maciej Dusza 3 4557045.0459 98027
Linda Ecoffey 4 3857163.6332 98027
Mark Erickson 5 3827950.238 98055
Terry Eminhizer 6 3587378.4257 98055
Michael Emanuel 7 3189356.2465 98055
Jauna Elson 8 3018725.4858 98055
Carol Elliott 9 2811012.7151 98027
Janeth Esteves 10 2241204.0424 98055
Martha Espinoza 11 1931620.1835 98055
Carla Eldridge 12 1764938.9859 98027
Twanna Evans 13 1758385.926 98055
（13行受影响）
*/
/*以下示例将返回行号为50到60（含）的行，并以OrderDate排序。
*/
USEAdventureWorks;
GO
WITHOrderedOrdersAS
（SELECTSalesOrderID,OrderDate,
ROW_NUMBER（）OVER（orderbyOrderDate）asRowNumber
FROMSales.SalesOrderHeader）
SELECT*
FROMOrderedOrders
WHERERowNumberbetween50and60;
/*
SalesOrderIDOrderDate RowNumber
-------------------------------------------------------
43708 2001-07-0300:
00:
00.00050
43709 2001-07-0300:
00:
00.00051
43710 2001-07-0300:
00:
00.00052
43711 2001-07-0400:
00:
00.00053
43712 2001-07-0400:
00:
00.00054
43713 2001-07-0500:
00:
00.00055
43714 2001-07-0500:
00:
00.00056
43715 2001-07-0500:
00:
00.00057
43716 2001-07-0500:
00:
00.00058
43717 2001-07-0500:
00:
00.00059
43718 2001-07-0600:
00:
00.00060
（11行受影响）
*/
--------------------------------------------------------------
RANK（）
说明：
返回结果集的分区内每行的排名。
行的排名是相关行之前的排名数加一。
语法：
RANK（）OVER（[]）
备注：
如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。
例如，如果两位顶尖销售员具有同样的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
GO
/*
ProductID Name LocationIDQuantityRANK
---------------------------------------------------------------------------------------------------
1 AdjustableRace 6 324 71
1 AdjustableRace 1 408 78
1 AdjustableRace 50 353 117
2 BearingBall 6 318 67
2 BearingBall 1 427 85
2 BearingBall 50 364 122
3 BBBallBearing 50 324 106
3 BBBallBearing 1 585 110
3 BBBallBearing 6 443 115
4 HeadsetBallBearings 1 512 99
4 HeadsetBallBearings 6 422 108
4 HeadsetBallBearings 50 388 140
316 Blade 10 388 33
......
（1069行受影响）
*/
--接上.
-------------------------------------------------------------------------------------
DENSE_RANK（）
说明：
返回结果集分区中行的排名，在排名中没有任何间断。
行的排名等于所讨论行之前的所有排名数加一。
语法：
DENSE_RANK（）OVER（[]）
备注：
如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。
例如，如果两位顶尖销售员具有相同的SalesYTD值，则他们将并列第一。
接下来SalesYTD最高的销售人员排名第二。
该排名等于该行之前的所有行数加一。
因此，DENSE_RANK函数返回的数字没有间断，并且始终具有连续的排名。
整个查询所用的排序顺序确定了各行在结果中的显示顺序。
这说明排名第一的行可以不是分区中的第一行。
参数：
：
将FROM子句所生成的结果集划分为数个将应用DENSE_RANK函数的分区。
：
确定将DENSE_RANK值应用于分区中各行的顺序。
返回类型：
bigint
示例：
/*以下示例返回各位置上产品数量的DENSE_RANK。
*/
USEAdventureWorks;
GO
SELECT i.ProductID,p.Name,i.LocationID,i.Quantity,DENSE_RANK（）OVER（PARTITIONBYi.LocationIDorderbyi.Quantity）asDENSE_RANK
FROMProduction.ProductInventoryiJOINProduction.ProductpONi.ProductID=p.ProductID
ORDERBYName;
GO
/*
ProductID Name LocationIDQuantityDENSE_RANK
---------------------------------------------------------------------------------------------------
1 AdjustableRace 1 408 57
1 AdjustableRace 6 324 52
1 AdjustableRace 50 353 82
879 All-PurposeBikeStand 7 144 34
712 AWCLogoCap 7 288 38
3 BBBallBearing 50 324 74
3 BBBallBearing 6 443 81
3 BBBallBearing 1 585 82
*/
将上面三个函数放在一起计算，更能明显看出各个函数的功能。
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）ASdenserank
FROMrankorder
ORDERBYqty
/*
orderid qty rownumber rank denserank
----------------------------------------------------------------------------------
30001 10 1 1 1
10001 10 2 1 1
10006 10 3 1 1
40005 10 4 1 1
30003 15 5 5 2
30004 20 6 6 3
20002 20 7 6 3
20001 20 8 6 3
10005 30 9 9 4
30007 30 10 9 4
30007 30 11 9 4
40001 40 12 12 5
（12行受影响）
*/
--对两个列qty,orderid进行的排序
SELECTorderid,qty,
ROW_NUMBER（）OVER（ORDERBYqty,orderid）ASrownumber,
RANK（） OVER（ORDERBYqty,orderid）ASrank,
DENSE_RANK（）OVER（ORDERBYqty,orderid）ASdenserank
FROMrankorder
ORDERBYqty,orderid
droptablerankorder
/*
orderid qty rownumber rank denserank
----------------------------------------------------------------------------------
10001 10 1 1 1
10006 10 2 2 2
30001 10 3 3 3
40005 10 4 4 4
30003 15 5 5 5
20001 20 6 6 6
20002 20 7 7 7
30004 20 8 8 8
10005 30 9 9 9
30007 30 10 10 10
30007 30 11 10 10
40001 40 12 12 11
（12行受影响）
*/
--示例数据
CREATETABLEtb（Namevarchar（10）,Scoredecimal（10,2））
INSERTtbSELECT'aa',99
UNIONALLSELECT'bb',56
UNIONALLSELECT'cc',56
UNIONALLSELECT'dd',77
UNIONALLSELECT'ee',78
UNIONALLSELECT'ff',76
UNIONALLSELECT'
升级会员 