SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法


ROW_NUMBER()

说明:返回结果集分区内行的序列号,每个分区的第一行从
1 开始。
语法:ROW_NUMBER ()
OVER ( [ <partition_by_clause> ] <order_by_clause> ) 。
备注:
ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
参数:
<partition_by_clause> :将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
<order_by_clause>:确定将 ROW_NUMBER 值分配给分区中的行的顺序。
返回类型:
bigint

示例:
/*以下示例将根据年初至今的销售额,返回 AdventureWorks 中销售人员的 ROW_NUMBER。*/

USE AdventureWorks
GO
SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0
/*
FirstName LastName Row Number 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 排序。*/
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER()
OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;
/*
SalesOrderID OrderDate RowNumber
------------ ----------------------- --------------------
43708 2001-07-03 00:00:00.000 50
43709 2001-07-03 00:00:00.000 51
43710 2001-07-03 00:00:00.000 52
43711 2001-07-04 00:00:00.000 53
43712 2001-07-04 00:00:00.000 54
43713 2001-07-05 00:00:00.000 55
43714 2001-07-05 00:00:00.000 56
43715 2001-07-05 00:00:00.000 57
43716 2001-07-05 00:00:00.000 58
43717 2001-07-05 00:00:00.000 59
43718 2001-07-06 00:00:00.000 60
(11 行受影响)
*/

--------------------------------------------------------------
RANK()

说明:返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。
语法:RANK ()
OVER ( [ < partition_by_clause > ] < order_by_clause > )
备注:如果两个或多个行与一个排名关联,则每个关联行将得到相同的排名。
例如,如果两位顶尖销售员具有同样的 SalesYTD 值,他们将并列第一。
由于已有两行排名在前,所以具有下一个最大 SalesYTD 的销售人员将排名第三。
因此,RANK 函数并不总返回连续整数。
用于整个查询的排序顺序决定了行在结果集中的显示顺序。这也隐含了行在每个分区中的排名。
参数:
< partition_by_clause > :将 FROM 子句生成的结果集划分为要应用 RANK 函数的分区。
< order_by_clause >:确定将 RANK 值应用于分区中的行时所基于的顺序。
返回类型:
bigint

示例:
/*以下示例按照数量对清单中的产品进行了排名。行集按 LocationID 分区,按 Quantity 排序。
USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as RANK
FROM Production.ProductInventory i JOIN Production.Product p
ON i.ProductID = p.ProductID
ORDER BY p.Name
GO
/*
ProductID Name LocationID Quantity RANK
----------- -------------------------------------------------- ---------- -------- --------------------
1 Adjustable Race 6 324 71
1 Adjustable Race 1 408 78
1 Adjustable Race 50 353 117
2 Bearing Ball 6 318 67
2 Bearing Ball 1 427 85
2 Bearing Ball 50 364 122
3 BB Ball Bearing 50 324 106
3 BB Ball Bearing 1 585 110
3 BB Ball Bearing 6 443 115
4 Headset Ball Bearings 1 512 99
4 Headset Ball Bearings 6 422 108
4 Headset Ball Bearings 50 388 140
316 Blade 10 388 33
......
(1069 行受影响)
*/

SQL code

--接上.
--
-----------------------------------------------------------------------------------
DENSE_RANK()

说明:返回结果集分区中行的排名,在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。
语法:DENSE_RANK ()
OVER ( [ < partition_by_clause > ] < order_by_clause > )
备注:如果有两个或多个行受同一个分区中排名的约束,则每个约束行将接收相同的排名。
例如,如果两位顶尖销售员具有相同的 SalesYTD 值,则他们将并列第一。
接下来 SalesYTD 最高的销售人员排名第二。该排名等于该行之前的所有行数加一。
因此,DENSE_RANK 函数返回的数字没有间断,并且始终具有连续的排名。
整个查询所用的排序顺序确定了各行在结果中的显示顺序。这说明排名第一的行可以不是分区中的第一行。
参数:
< partition_by_clause > :将 FROM 子句所生成的结果集划分为数个将应用 DENSE_RANK 函数的分区。
< order_by_clause >:确定将 DENSE_RANK 值应用于分区中各行的顺序。
返回类型:
bigint

示例:
/*以下示例返回各位置上产品数量的 DENSE_RANK。 */
USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, DENSE_RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as DENSE_RANK
FROM Production.ProductInventory i JOIN Production.Product p ON i.ProductID = p.ProductID
ORDER BY Name;
GO
/*
ProductID Name LocationID Quantity DENSE_RANK
----------- -------------------------------------------------- ---------- -------- --------------------
1 Adjustable Race 1 408 57
1 Adjustable Race 6 324 52
1 Adjustable Race 50 353 82
879 All-Purpose Bike Stand 7 144 34
712 AWC Logo Cap 7 288 38
3 BB Ball Bearing 50 324 74
3 BB Ball Bearing 6 443 81
3 BB Ball Bearing 1 585 82
*/

-------------------------------------------------------------------------------------------------------
将上面三个函数放在一起计算,更能明显看出各个函数的功能。

CREATE TABLE rankorder(orderid INT,qty INT)
INSERT rankorder VALUES(30001,10)
INSERT rankorder VALUES(10001,10)
INSERT rankorder VALUES(10006,10)
INSERT rankorder VALUES(40005,10)
INSERT rankorder VALUES(30003,15)
INSERT rankorder VALUES(30004,20)
INSERT rankorder VALUES(20002,20)
INSERT rankorder VALUES(20001,20)
INSERT rankorder VALUES(10005,30)
INSERT rankorder VALUES(30007,30)
INSERT rankorder VALUES(40001,40)
INSERT rankorder VALUES(30007,30)
GO
--对一个列qty进行的排序
SELECT orderid,qty,
ROW_NUMBER()
OVER(ORDER BY qty) AS rownumber,
RANK()
OVER(ORDER BY qty) AS rank,
DENSE_RANK()
OVER(ORDER BY qty) AS denserank
FROM rankorder
ORDER BY qty
/*
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进行的排序
SELECT orderid,qty,
ROW_NUMBER()
OVER(ORDER BY qty,orderid) AS rownumber,
RANK()
OVER(ORDER BY qty,orderid) AS rank,
DENSE_RANK()
OVER(ORDER BY qty,orderid) AS denserank
FROM rankorder
ORDER BY qty,orderid
drop table rankorder
/*
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 行受影响)
*/



相关阅读:
asp 获取access系统表,查询等操作代码
IE8 原生JSON支持
MYSQL教程:缓慢的drop table 操作
AJAX在移动领域威胁到Java ME了么?
调整“Oracle”数据库服务器的性能
javascript数组使用调用方法汇总
用Jquery实现多级下拉框无刷新的联动
PHP Ajax实现页面无刷新发表评论
用vbs遍历文件并随机显示的脚本
CSS教程:CSS3新功能和新特性
AJAX框架JSON-RPC-Java 1.0rc2 发布
让GridView只更新某些特定的数据的方法
js日历功能对象
利用正则快速找出两个字符串的不同字符
快速导航
PHP MySQL HTML CSS JavaScript MSSQL AJAX .NET JSP Linux Mac ASP 服务器 SQL jQuery C# C++ java Android IOS oracle MongoDB SQLite wamp 交通频道 作文范文 新县-沟帮子 汉口-当阳 广水-商丘 麻柳-达州 自贡-临江溪 南江口-富宁 广汉北-绵阳 孙镇-清涧县 金华-德州 大林-蔡家沟 南杂木-鸭园 永济北-太原南 长沙-沂水 汉口-潢川 商丘南-济南东 鞍山-双城堡 丹凤-温州 公主岭-淮滨 哈尔滨-平顶山西 香坊-穆棱 哈密-衡水 潍坊-南京 清涧县-南昌西 赤壁-雷州 张掖-荆门 亚布力-哈尔滨西 商丘南-进贤 嘉兴-镇远 塔哈-绥中 永康南-江宁 清水-西安南 大民屯-大石桥 漯河-金华 白城-滦县 嘉善南-常州 饶阳-沈阳北 绥中-麻城 瑞安-蚌埠南 河源-共青城 保定-达州 成都-酉阳 广州南-贺州 齐齐哈尔-巨宝 曲靖-湘潭 霸州西-凯里 嘉善-泰和 吕梁-绵阳 邹城-滦县 斜河涧-宣化 哈尔滨-柳园 恩施-杭州 醴陵-湘乡 汝州-甘洛 曲阜东-婺源 兴城-大石桥 高台-长春 南阳-渑池 昌黎-尚志 黄口-邳州 柳州-包头 鹰潭北-瑞昌 胶州-静海 城子坦-丹东 潍坊-太原 咸宁-金州 江津-达拉特西 诸暨-缙云 温岭-陆丰 昌图-驻马店 宜春-三明北 临城-宣汉 宁安-吉林 开原-保康 百色-桂平 沙日乃-兴和 巴林-开通 彰武-葫芦岛 道州-许昌 北京西-衡水 大庆东-亚布力 邯郸至合肥要多少钱坐火车列车坐哪几班
从济南列车火车站怎样到莱芜
天津到厦门特价机票
D198沈阳到上海的动车 到上海哪个站 在虹桥还是浦东
从徐州到石浦怎么坐车
上海列车票预定电话谁知道上海南到贵阳的k111次列车最早的要
方大同今天春晚多少小时好久出来
四川宜宾到 汶川有多少公里的距离
从成都列车火车站 到成都富士康做直达车要多少时间丫 还有成
路过南京——荷泽的火车列车要多少钱
请问金华到兰溪的客车汽车最晚几点
从宁波去山东泰安怎么去坐什么更实惠
从无锡去天津坐哪列火车列车比较好,票价多少
南宁琅东汽车站有车开往广东德庆吗
张家口西站能买到北京去厦门的火车列车票吗
常熟客车汽车站到达南京客车汽车站需要多久?
怎么订列车票从河南洛阳出发到广州的列车票要怎么订列车
荆门到松滋怎么坐车_荆门到松滋怎么坐车
青岛至天津长途票价钱数是多少几个小时到
本人常年有湛江虾仁·但是不知道怎样走销路
哪个大哥大姐 可以告诉我太原到徐州要多少长时间。谢谢了
怎么没有西安到濮阳的火车票
2011年西溪五常龙舟大会什么时候开始从龙驹坞坐公交怎么去
襄樊到九江的长途客车经过黄石停在哪个车站?
合肥市从801开始到和平路的公交车是哪几路啊?
查火车列车票车次:明天从郑州到邯郸的最早车
2008年8月28日杭州城站到宁波的火车时间表
南京雨花台风景区修建于什么时候?南京在古代的名称叫什么,...
长春列车站到吉林大学北区7舍怎么走
列车火车托运广州至无锡

Copyright © 2016 phpStudy |