wufeng4552進:謝謝你對排序函數的解釋 http://topic.csdn.net/u/20090120/10/9288e39c-7fbc-4538-b11b-fff71147fc52.htmlRANK(),DENSE_RANK(),ROW_NUMBER(),NTILE()謝謝。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 ROW_NUMBER、RANK、DENSE_RANK的用法(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳)SQL Server 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 AdventureWorksGOSELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCodeFROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactIDJOIN Person.Address a ON a.AddressID = c.ContactIDWHERE TerritoryID IS NOT NULL AND SalesYTD <> 0/*FirstName LastName Row Number SalesYTD PostalCode--------- ---------- ---------- ------------ ----------------------------Shelley Dyck 1 5200475.2313 98027Gail Erickson 2 5015682.3752 98055Maciej Dusza 3 4557045.0459 98027Linda Ecoffey 4 3857163.6332 98027Mark Erickson 5 3827950.238 98055Terry Eminhizer 6 3587378.4257 98055Michael Emanuel 7 3189356.2465 98055Jauna Elson 8 3018725.4858 98055Carol Elliott 9 2811012.7151 98027Janeth Esteves 10 2241204.0424 98055Martha Espinoza 11 1931620.1835 98055Carla Eldridge 12 1764938.9859 98027Twanna Evans 13 1758385.926 98055(13 行受影响)*/ /*以下示例将返回行号为 50 到 60(含)的行,并以 OrderDate 排序。*/ USE AdventureWorks;GOWITH OrderedOrders AS(SELECT SalesOrderID, OrderDate,ROW_NUMBER() OVER (order by OrderDate)as RowNumberFROM Sales.SalesOrderHeader ) SELECT * FROM OrderedOrders WHERE RowNumber between 50 and 60;/*SalesOrderID OrderDate RowNumber------------ ----------------------- --------------------43708 2001-07-03 00:00:00.000 5043709 2001-07-03 00:00:00.000 5143710 2001-07-03 00:00:00.000 5243711 2001-07-04 00:00:00.000 5343712 2001-07-04 00:00:00.000 5443713 2001-07-05 00:00:00.000 5543714 2001-07-05 00:00:00.000 5643715 2001-07-05 00:00:00.000 5743716 2001-07-05 00:00:00.000 5843717 2001-07-05 00:00:00.000 5943718 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;GOSELECT i.ProductID, p.Name, i.LocationID, i.Quantity, RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as RANKFROM Production.ProductInventory i JOIN Production.Product p ON i.ProductID = p.ProductIDORDER BY p.NameGO/*ProductID Name LocationID Quantity RANK----------- -------------------------------------------------- ---------- -------- --------------------1 Adjustable Race 6 324 711 Adjustable Race 1 408 781 Adjustable Race 50 353 1172 Bearing Ball 6 318 672 Bearing Ball 1 427 852 Bearing Ball 50 364 1223 BB Ball Bearing 50 324 1063 BB Ball Bearing 1 585 1103 BB Ball Bearing 6 443 1154 Headset Ball Bearings 1 512 994 Headset Ball Bearings 6 422 1084 Headset Ball Bearings 50 388 140316 Blade 10 388 33......(1069 行受影响)*/ ------------------------------------------------------------------------------------- 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;GOSELECT i.ProductID, p.Name, i.LocationID, i.Quantity, DENSE_RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as DENSE_RANKFROM Production.ProductInventory i JOIN Production.Product p ON i.ProductID = p.ProductIDORDER BY Name;GO/*ProductID Name LocationID Quantity DENSE_RANK----------- -------------------------------------------------- ---------- -------- --------------------1 Adjustable Race 1 408 571 Adjustable Race 6 324 521 Adjustable Race 50 353 82879 All-Purpose Bike Stand 7 144 34712 AWC Logo Cap 7 288 383 BB Ball Bearing 50 324 743 BB Ball Bearing 6 443 813 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 denserankFROM rankorderORDER BY qty/*orderid qty rownumber rank denserank----------- ----------- -------------------- -------------------- --------------------30001 10 1 1 110001 10 2 1 110006 10 3 1 140005 10 4 1 130003 15 5 5 230004 20 6 6 320002 20 7 6 320001 20 8 6 310005 30 9 9 430007 30 10 9 430007 30 11 9 440001 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 denserankFROM rankorderORDER BY qty,orderiddrop table rankorder/*orderid qty rownumber rank denserank----------- ----------- -------------------- -------------------- --------------------10001 10 1 1 110006 10 2 2 230001 10 3 3 340005 10 4 4 430003 15 5 5 520001 20 6 6 620002 20 7 7 730004 20 8 8 810005 30 9 9 930007 30 10 10 1030007 30 11 10 1040001 40 12 12 11(12 行受影响)*/ NTILE 的用法(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳)将有序分区中的行分发到指定数目的组中。各个组有编号,编号从一开始。对于每一个行,NTILE 将返回此行所属的组的编号。--------------------------------------------------------------------------------------------------------------语法:NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )备注:如果分区的行数不能被 expression 整除,则将导致一个成员有两种大小不同的组。 按照 OVER 子句指定的顺序,较大的组排在较小的组前面。 例如,如果总行数是 53,存储桶数是 5,则前三个存储桶每个包含 11 行,其余两个存储桶每个包含 10 行。 另一方面,如果总行数可被存储桶数整除,则行数将在存储桶之间平均分布。 例如,如果总行数为 50,有五个存储桶,则每个存储桶将包含 10 行。参数:integer_expression:一个正整数常量,用于指定每个分区必须被划分成的存储桶的数量。 integer_expression 的类型可以为 bigint。 <partition_by_clause> :将 FROM 子句生成的结果集划分成 RANK 函数适用的分区。 < order_by_clause >:确定 NTILE 值分配到分区中各行的顺序。返回类型:bigint示例:/*以下示例将在四个存储桶中分布行。由于总行数不能被存储桶数整除,因此第一个存储桶将包含四行,其余的存储桶每个包含三行。*/USE AdventureWorks;GOSELECT c.FirstName, c.LastName, NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile', s.SalesYTD, a.PostalCodeFrom Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactIDJOIN Person.Address a ON a.AddressID = c.ContactIDWHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;GO/*FirstName LastName Quartile SalesYTD PostalCode------------ ------------ ------------------- -------------------- ----------Shelley Dyck 1 5200475.2313 98027Gail Erickson 1 5015682.3752 98055Maciej Dusza 1 4557045.0459 98027Linda Ecoffey 1 3857163.6332 98027Mark Erickson 2 3827950.238 98055Terry Eminhizer 2 3587378.4257 98055Michael Emanuel 2 3189356.2465 98055Jauna Elson 3 3018725.4858 98055Carol Elliott 3 2811012.7151 98027Janeth Esteves 3 2241204.0424 98055Martha Espinoza 4 1931620.1835 98055Carla Eldridge 4 1764938.9859 98027Twanna Evans 4 1758385.926 98055(13 行受影响)*/ 关于查询的一点问题 REFERENCES的 ON DELETE CASCADE 初学存储过程,问一简单问题,谢谢 存储过程问题 请看一下我的触发器有什么毛病! 一个SQL查询的问题 请高手支招:将.xml配置文件Data Source改为本机IP时,数据库(SQL2005)链接失败 求教怎么组合这样的 Sql 语句?答者有分 将微软的SQL SERVER补丁能否集成到原文件 什么语言最支持数据库呢? 有一个小疑问 如何取得一段时间内所有日期的列表?
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳)SQL Server 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 行受影响)
*/ -------------------------------------------------------------------------------------
语法: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 行受影响)
*/
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳)将有序分区中的行分发到指定数目的组中。各个组有编号,编号从一开始。对于每一个行,NTILE 将返回此行所属的组的编号。--------------------------------------------------------------------------------------------------------------语法:NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )
备注:如果分区的行数不能被 expression 整除,则将导致一个成员有两种大小不同的组。
按照 OVER 子句指定的顺序,较大的组排在较小的组前面。
例如,如果总行数是 53,存储桶数是 5,则前三个存储桶每个包含 11 行,其余两个存储桶每个包含 10 行。
另一方面,如果总行数可被存储桶数整除,则行数将在存储桶之间平均分布。
例如,如果总行数为 50,有五个存储桶,则每个存储桶将包含 10 行。
参数:integer_expression:一个正整数常量,用于指定每个分区必须被划分成的存储桶的数量。
integer_expression 的类型可以为 bigint。
<partition_by_clause> :将 FROM 子句生成的结果集划分成 RANK 函数适用的分区。
< order_by_clause >:确定 NTILE 值分配到分区中各行的顺序。
返回类型:bigint示例:
/*以下示例将在四个存储桶中分布行。由于总行数不能被存储桶数整除,因此第一个存储桶将包含四行,其余的存储桶每个包含三行。*/
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile', 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;
GO
/*
FirstName LastName Quartile SalesYTD PostalCode
------------ ------------ ------------------- -------------------- ----------
Shelley Dyck 1 5200475.2313 98027
Gail Erickson 1 5015682.3752 98055
Maciej Dusza 1 4557045.0459 98027
Linda Ecoffey 1 3857163.6332 98027
Mark Erickson 2 3827950.238 98055
Terry Eminhizer 2 3587378.4257 98055
Michael Emanuel 2 3189356.2465 98055
Jauna Elson 3 3018725.4858 98055
Carol Elliott 3 2811012.7151 98027
Janeth Esteves 3 2241204.0424 98055
Martha Espinoza 4 1931620.1835 98055
Carla Eldridge 4 1764938.9859 98027
Twanna Evans 4 1758385.926 98055
(13 行受影响)
*/