ROWNUMBERRANKDENSERANKLAGWord下载.docx
《ROWNUMBERRANKDENSERANKLAGWord下载.docx》由会员分享,可在线阅读,更多相关《ROWNUMBERRANKDENSERANKLAGWord下载.docx(14页珍藏版)》请在冰豆网上搜索。
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
Linda
Ecoffey
4
3857163.6332
Mark
5
3827950.238
Terry
Eminhizer
6
3587378.4257
Michael
Emanuel
7
3189356.2465
Jauna
Elson
8
3018725.4858
Carol
Elliott
9
2811012.7151
Janeth
Esteves
10
2241204.0424
Martha
Espinoza
11
1931620.1835
Carla
Eldridge
12
1764938.9859
Twanna
Evans
13
1758385.926
(13行受影响)
/*以下示例将返回行号为50到60(含)的行,并以OrderDate排序。
*/
USEAdventureWorks;
WITHOrderedOrdersAS
(SELECTSalesOrderID,OrderDate,
ROW_NUMBER()OVER(orderbyOrderDate)asRowNumber
FROMSales.SalesOrderHeader)
SELECT*
FROMOrderedOrders
WHERERowNumberbetween50and60;
SalesOrderIDOrderDate
RowNumber
-------------------------------------------------------
43708
2001-07-0300:
00:
00.00050
43709
00.00051
43710
00.00052
43711
2001-07-0400:
00.00053
43712
00.00054
43713
2001-07-0500:
00.00055
43714
00.00056
43715
00.00057
43716
00.00058
43717
00.00059
43718
2001-07-0600:
00.00060
(11行受影响)
--------------------------------------------------------------
RANK()
返回结果集的分区内每行的排名。
行的排名是相关行之前的排名数加一。
RANK()OVER([<
partition_by_clause>
order_by_clause>
)
如果两个或多个行与一个排名关联,则每个关联行将得到相同的排名。
例如,如果两位顶尖销售员具有同样的SalesYTD值,他们将并列第一。
由于已有两行排名在前,所以具有下一个最大SalesYTD的销售人员将排名第三。
因此,RANK函数并不总返回连续整数。
用于整个查询的排序顺序决定了行在结果集中的显示顺序。
这也隐含了行在每个分区中的排名。
将FROM子句生成的结果集划分为要应用RANK函数的分区。
确定将RANK值应用于分区中的行时所基于的顺序。
bigint
/*以下示例按照数量对清单中的产品进行了排名。
行集按LocationID分区,按Quantity排序。
SELECTi.ProductID,p.Name,i.LocationID,i.Quantity,RANK()OVER(PARTITIONBYi.LocationIDorderbyi.Quantity)asRANK
FROMProduction.ProductInventoryiJOINProduction.Productp
ONi.ProductID=p.ProductID
ORDERBYp.Name
ProductID
Name
LocationIDQuantityRANK
---------------------------------------------------------------------------------------------------
1
AdjustableRace
324
71
408
78
50
353
117
2
BearingBall
318
67
427
85
364
122
3
BBBallBearing
106
585
110
443
115
4
HeadsetBallBearings
512
99
422
108
388
140
316
Blade
33
......
(1069行受影响)
--接上.
-------------------------------------------------------------------------------------
DENSE_RANK()
返回结果集分区中行的排名,在排名中没有任何间断。
行的排名等于所讨论行之前的所有排名数加一。
DENSE_RANK()OVER([<
如果有两个或多个行受同一个分区中排名的约束,则每个约束行将接收相同的排名。
例如,如果两位顶尖销售员具有相同的SalesYTD值,则他们将并列第一。
接下来SalesYTD最高的销售人员排名第二。
该排名等于该行之前的所有行数加一。
因此,DENSE_RANK函数返回的数字没有间断,并且始终具有连续的排名。
整个查询所用的排序顺序确定了各行在结果中的显示顺序。
这说明排名第一的行可以不是分区中的第一行。
将FROM子句所生成的结果集划分为数个将应用DENSE_RANK函数的分区。
确定将DENSE_RANK值应用于分区中各行的顺序。
/*以下示例返回各位置上产品数量的DENSE_RANK。
*/
SELECT
i.ProductID,p.Name,i.LocationID,i.Quantity,DENSE_RANK()OVER(PARTITIONBYi.LocationIDorderbyi.Quantity)asDENSE_RANK
FROMProduction.ProductInventoryiJOINProduction.ProductpONi.ProductID=p.ProductID
ORDERBYName;
LocationIDQuantityDENSE_RANK
57
52
82
879
All-PurposeBikeStand
144
34
712
AWCLogoCap
288
38
74
81
将上面三个函数放在一起计算,更能明显看出各个函数的功能。
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)
--对一个列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
1
10001
10006
40005
30003
15
2
30004
20
3
20002
20001
10005
30
4
30007
40001
40
5
(12行受影响)
--对两个列qty,orderid进行的排序
ROW_NUMBER()OVER(ORDERBYqty,orderid)ASrownumber,
OVER(ORDERBYqty,orderid)ASrank,
DENSE_RANK()OVER(ORDERBYqty,orderid)ASdenserank
ORDERBYqty,orderid
droptablerankorder
6
7
8
9
10
11
--示例数据
CREATETABLEtb(Namevarchar(10),Scoredecimal(10,2))
INSERTtbSELECT'
aa'
99
UNIONALLSELECT'
bb'
56
cc'
dd'
77
ee'
78
ff'
76