SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法

2016-02-19 10:21 4 1 收藏

今天图老师小编给大家介绍下SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法,平时喜欢SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法的朋友赶紧收藏起来吧!记得点赞哦~

【 tulaoshi.com - 编程语言 】

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 Race1 408 57
1 Adjustable Race6 324 52
1 Adjustable Race50 353 82
879 All-Purpose Bike Stand 7 144 34
712 AWC Logo Cap 7 288 38
3 BB Ball Bearing50 324 74
3 BB Ball Bearing6 443 81
3 BB Ball Bearing1 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
----------- ----------- -------------------- -------------------- --------------------
3000110 1 1 1
1000110 2 1 1
1000610 3 1 1
4000510 4 1 1
3000315 5 5 2
3000420 6 6 3
2000220 7 6 3
2000120 8 6 3
1000530 9 9 4
3000730 10 9 4
3000730 11 9 4
4000140 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
----------- ----------- -------------------- -------------------- --------------------
1000110 1 1 1
1000610 2 2 2
3000110 3 3 3
4000510 4 4 4
3000315 5 5 5
2000120 6 6 6
2000220 7 7 7
3000420 8 8 8
1000530 9 9 9
3000730 10 10 10
3000730 11 10 10
4000140 12 12 11
(12 行受影响)
*/

来源:http://www.tulaoshi.com/n/20160219/1594381.html

延伸阅读
查询资料后知道有 sp_addlinkedserver 这个系统存储过程,可通过ODBC连接MySql,于是下载ODBC驱动,这个可通过MySql官网得到,地址:http://dev.mysql.com/downloads/。 安装驱动后配置数据源,这里注意,要用系统数据源,我在用户数据源中配置后数据源能连上MySql,但无法能过SqlServer连接,报错:Data source name not found and no defaul...
《最后的神迹》3个公会rank任务说明 Union of the Golden Chalice-金杯公会 RANK1 14号任务。 在Blackdale(ブラックデール)出现后 杀掉大鸟Dominator 进入后拉高镜头你会看到这个大鸟,如果没有请重新进入。不放心的话再往里走走。。不是太远的位置。   RANK2 16号任务 完成RANK1,进入过The Catacombs。 进入Lavafender后,右...
代码如下: --查看当前数据库状态 1为已经启用了全文索引 SELECT DATABASEPROPERTY ('数据库名','IsFulltextEnabled') --打开FullText功能 execute sp_fulltext_databse 'enable' --关闭此功能 execute sp_fulltext_databse 'disable' --创建全文索引目录 --删除全文目录 DROP FULLTEXT 目录名 CREATE FULLTEXT CATALOG 目录名 -- 每...
对于应用来说,我并不提倡人为给记录加锁,这样会惹来很多麻烦,况且锁并不能解决所有问题,如果你有这方面好的经验我们可以进一步交流。 对于应用来说,我并不提倡人为给记录加锁,这样会惹来很多麻烦,况且锁并不能解决所有问题,如果你有这方面好的经验我们可以进一步交流。 代码如下: set nocount on use tempdb go if (object_id (...
尽管从技术上讲,其它排名函数的计算与ROW_NUMBER类似,但它们的的实际应用却少很多。RANK和DENSE——RANK主要用于排名和积分。NTILE更多地用于分析。 先创建一个示例表: 代码如下: SET NOCOUNT ON USE [tempdb] IF OBJECT_ID('Sales')IS NOT NULL DROP TABLE sales CREATE TABLE Sales ( empid VARCHAR(10) NOT NULL PRIMARY KEY...

经验教程

500

收藏

19
微博分享 QQ分享 QQ空间 手机页面 收藏网站 回到头部