ROW_NUMBER、RANK、DENSE_RANK、NTILE的用法

 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()

说明：返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。
语法：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 行受影响)
*/

--示例数据
CREATE TABLE tb(Name varchar(10),Score decimal(10,2))
INSERT tb SELECT 'aa',99
UNION ALL SELECT 'bb',56
UNION ALL SELECT 'cc',56
UNION ALL SELECT 'dd',77
UNION ALL SELECT 'ee',78
UNION ALL SELECT 'ff',76
UNION ALL SELECT 'gg',78
UNION ALL SELECT 'ff',50
GO

--1. 名次生成方式1,Score重复时合并名次
SELECT *,Place=(SELECT COUNT(DISTINCT Score) FROM tb WHERE Score>=a.Score)
FROM tb a
ORDER BY Place
/*--结果
Name       Score        Place
---------------- ----------------- -----------
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        3
ff         76.00        4
bb         56.00        5
cc         56.00        5
ff         50.00        6
--*/

 

--2. 名次生成方式2,Score重复时保留名次空缺
SELECT *,Place=(SELECT COUNT(Score) FROM tb WHERE Score>a.Score)+1
FROM tb a
ORDER BY Place
/*--结果
Name       Score        Place
--------------- ----------------- -----------
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        4
ff         76.00        5
bb         56.00        6
cc         56.00        6
ff         50.00        8
--*/
create table cj(bj int,zf int)
insert into cj select 1, 98
insert into cj select 2, 97
insert into cj select 1, 96
insert into cj select 2, 96
insert into cj select 1, 95
insert into cj select 2, 94
insert into cj select 1, 94
insert into cj select 2, 94
insert into cj select 1, 93

select bj,
     zmc=(select count(distinct zf) from cj where zf>a.zf)+1,
     bmc=(select count(distinct zf) from cj where zf>a.zf and bj=a.bj)+1,
     zf
from cj a

go
drop table cj

/*
bj          zmc         bmc         zf         
----------- ----------- ----------- -----------
1           1           1           98
2           2           1           97
1           3           2           96
2           3           2           96
1           4           3           95
2           5           3           94
1           5           4           94
2           5           3           94
1           6           5           93

（所影响的行数为 9 行）
*/

 

 

SQL server 2005新增的几个函数,分别是row_number( )、rank( )、,DENSE_RANK( )、ntile( )下面以实例分别简单讲解。

1.row_number( )
         先来点数据,先建个表

SET NOCOUNT ON
CREATE TABLE Person(
FirstName VARCHAR(10),
Age INT,
Gender CHAR(1))
INSERT INTO Person VALUES ('Ted',23,'M')
INSERT INTO Person VALUES ('John',40,'M')
INSERT INTO Person VALUES ('George',6,'M')
INSERT INTO Person VALUES ('Mary',11,'F')
INSERT INTO Person VALUES ('Sam',17,'M')
INSERT INTO Person VALUES ('Doris',6,'F')
INSERT INTO Person VALUES ('Frank',38,'M')
INSERT INTO Person VALUES ('Larry',5,'M')
INSERT INTO Person VALUES ('Sue',29,'F')
INSERT INTO Person VALUES ('Sherry',11,'F')
INSERT INTO Person VALUES ('Marty',23,'F')
直接用例子说明问题:
SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age],
FirstName,
Age
FROM Person

出现的数据如下
Row Number by Age                FirstName            Age
--------------------------                 ----------            --------
1                                                Larry                   5
2                                                Doris                   6
3                                                George               6
4                                                Mary                   11
5                                                Sherry                 11
6                                                Sam                    17
7                                                Ted                     23
8                                                Marty                   23
9                                                Sue                     29
10                                              Frank                  38
11                                              John                    40
可以观察到,是根据年龄升序排列了，并且row_number()是给出了序列号了,这个序列号被重命名为Row Number by Age,与sql server2000对比:如果在sql server2000中实现相对麻烦一些，我们可以利用IDENTITY()函数实现，但IDENTITY()函数只能用在sql server2000临时表中，因此需要将数据检索到临时表里。select identity(int,1,1) as [Row Number by Age],FirstName,Age into #A from Person order by Ageselect * from #Adrop table #a如果不想按年龄排序,可以这样写SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set],
FirstName,
Age
FROM Person另外一个例子SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
FirstName,
Age,
Gender
FROM Person这里是按性别划分区间了，同一性别再按年龄来排序,输出结果如下

Partition by Gender         FirstName         Age                Gender
-------------------- ---------- ----------- ------
1                           Doris             6                  F
2                           Mary              11                 F
3                           Sherry            11                 F
4                           Sue               29                 F
1                           Larry             5                  M
2                           George            6                  M
3                           Sam               17                 M
4                           Ted               23                 M
5                           Marty             23                 M
6                           Frank             38                 M
7                           John              40                 M注意,姓名M开始,序号又从1,2,3开始了

 2.RANK( )函数         先看例子SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age],
FirstName,
Age
FROM Person输出如下:Rank by Age                 FirstName         Age
-------------------- ---------- -----------
1                           Larry             5
2                           Doris             6
2                           George            6
4                           Mary              11
4                           Sherry            11
6                           Sam               17
7                           Ted               23
7                           Marty             23
9                           Sue               29
10                          Frank             38
11                          John              40看到了么,同年岭的话,将有相同的顺序,顺序成1,2,2,4了。与sql server2000对比:出现了RANK()函数实在是方便,在sql server2000里实现排序并列的问题麻烦很多。select [Rank by Age]=isnull((select count(*) from person where Age>A.Age),0)+1,FirstName,Age from Person A order by [Rank by Age] SELECT RANK() OVER(PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],FirstName, Age, Gender FROM Person输出为Partition by Gender         FirstName         Age                Gender
-------------------- ---------- ----------- ------
1                           Doris             6                  F
2                           Mary              11                 F
2                           Sherry            11                 F
4                           Sue               29                 F
1                           Larry             5                  M
2                           George            6                  M
3                           Sam               17                 M
4                           Ted               23                 M
4                           Marty             23                 M
6                           Frank             38                 M
7                           John              40                 M可以看到,按性别分组了,每个性别分组里,继续是用了rank( )函数

3.DENSE_RANK( )函数
         SELECT DENSE_RANK() OVER (ORDER BY Age) AS [Dense Rank by Age],
         FirstName,
         Age
         FROM Person

输出结果为:
Dense Rank by Age          FirstName        Age
-------------------- ---------- -----------
1                          Larry            5
2                          Doris            6
2                          George           6
3                          Mary             11
3                          Sherry           11
4                          Sam              17
5                          Ted              23
5                          Marty            23
6                          Sue              29
7                          Frank            38
8                          John             40

看到了么,和rank函数区别是,顺序始终是连续的,Doris 和George同年,都是排第2位,但之后的mary不象rank函数那样排第4,而是排第3位了

4.ntile( )函数
SELECT FirstName,
Age,
NTILE(3) OVER (ORDER BY Age) AS [Age Groups]
FROM Person

输出结果：
FirstName        Age               Age Groups
---------- ----------- --------------------
Larry                5                  1
Doris                6                  1
George            6                  1
Mary                11                1
Sherry             11                 2
Sam                17                 2
Ted                 23                 2
Marty              23                 2
Sue                29                 3
Frank             38                 3
John               40                 3
这个函数按照ntile(n)中的N,把记录强制分成多少段,11条记录现在分成3段了,lary到mary是第1段,sherry到maty是第2段,sue到john是第3段了。