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 服务器 CMS SQL jQuery C# C++ java Android IOS oracle MongoDB PostgreSQL SQLite 交通频道 G4722 G1875 G215 G569 G421 G6733 G7577 G8906 G1235 G4916 G7291 G1953 G245 G662 G1570 G6285 G719 G1836 G1346 G4781 G4908 G289 G6781 G9290 G7358 G1928 G1815 G325 G132 G4901 G6012 G6290 G7131 G5367 G184 G151 G5303 G1136 G6481 G7028 G575 G1744 G7660 G7693 G2344 G4937 G1234 G1814 G6252 G1492 G253 G2926 G883 G9275 G1231 G556 G241 G1306 G7646 G8103 G600 G1858 G9678 G6160 G7156 G825 G1125 G7249 G1809 G1350 G432 G9466 G7067 G785 G6404 G4663 G7008 G150 G823 G1514 G7529 G1201 G2353 G205 G7629 G9409 G6147 G677 G390 G8016 G9239 G456 G828 G8045 G491 G7145 G397 G7012 G1021 G6482 G2322 G7264 G1301 G9247 G96 G1294 G7133 G4824 G7005 G1653 G5307 G1213 G822 G4837 G1422 G411 G6227 G1571 G359 G1882 G6074 G7678 G21 G7077 G1272 G8918 G9645 G461 G1254 G1846 G8021 G7303 G1104 G76 G82 G621 G218 G8533 G2341 G8543 G555 G8013 G4802 G1364 G1153 G1342 G1861 G8905 G590 G4780 G668 G9261 G1304 G1638 G1395 G2914 G8003 G7158 G1833 G1873 G8128 G1856 G1841 G8709 G7346 G4612 G2103 G835 G8712 G381 G7240 G8932 G507 G29 G4054 G6273 G6752 G426 G211 G9473 G7119 G2333 G1567 G6153 G360 G4011 G5301 G7648 G8010 G8015 G6706 G614 G423 G8557 G9465 G72 G6018 G8901 G7030 G123

丹东 云霄 辽中 德阳 克拉玛依 惠山 招远 昭通 铁岭西 延吉西 军粮城北 定西 晋中 许昌东 郫县 诏安 七台河 高碑店东 南昌 延安 敦化 铜陵北 嵩明 鲘门 扬中 龙里北 舟山 洛阳 运城北 鞍山 西昌 邵阳北 绍兴 白山 三明 肇东 陵水 衡山西 嘉善 宜都 泰兴 泉州 汉口 东胜西 昌图西 锦州南 安阳东 怀化 黄南 亚龙湾 扬州 温州 南翔北 福安 金山北 永川东 安达 曲阜东 郑州西 天门 绍兴北 涪陵北 阳泉北 三亚 葫芦岛北 徐州 阳江 辽源 新泰 阿坝 孝感北 三穗 金寨 保山 高安 安阳 牟平 西双版纳 信阳 繁昌西 哈尔滨北 达州 新余 沈阳南 四平 扶余北 伊宁 郴州西 济源 水家湖 民权北 福鼎 如皋 奉化 全州南 安庆 太姥山 武汉 乐清 皮口 武昌 茂名 邯郸 资阳 马鞍山 三水南 泰安 包头东 衡阳东 南丰 仙桃西 安吉 罗源 山海关 平湖 惠州 资阳北 淄博 丹阳 莱州 巴东 关岭 盐城 锦州 格尔木 益阳 大英东 吉林 湛江 临安 襄汾西 渑池南 当涂东 辽阳 徐水 贺州 韶关 光明城 邯郸东 普安县 南江口 铜川 五龙背东 张家港 烟台南 萍乡北 青堆 长乐 江门 台州 衡水 湘潭北 闽清北 高邑西 盖州西 石柱县 潮汕 肇庆 泰康 邵东 湖州 余姚 平凉 宜宾 增城 沧州 都匀 防城港 鹰潭北 海东西 福田 余姚北 岳池 广州北 南安 蓬莱 瓦房店西 李石寨 葛店南 海安 无锡东 上饶 通辽 四会 桂林西 砀山南 兰州 滨海 龙口 绅坊 莱西 石林西 深圳 大连北 成都 上海西 孝感 杏树屯 德清 嘉兴

Copyright © 2016 phpStudy |