SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法

2020-10-30 19:10:08

字體：大中小

來源：轉載

供稿：網友

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 行受影響)
*/