SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法

--接上.
-------------------------------------------------------------------------------------
DENSE_RANK()

說明：返回結(jié)果集分區(qū)中行的排名，在排名中沒有任何間斷。行的排名等于所討論行之前的所有排名數(shù)加一。
語(yǔ)法：DENSE_RANK () OVER ( [  partition_by_clause > ]  order_by_clause > )
備注：如果有兩個(gè)或多個(gè)行受同一個(gè)分區(qū)中排名的約束，則每個(gè)約束行將接收相同的排名。
   例如，如果兩位頂尖銷售員具有相同的 SalesYTD 值，則他們將并列第一。
   接下來 SalesYTD 最高的銷售人員排名第二。該排名等于該行之前的所有行數(shù)加一。
   因此，DENSE_RANK 函數(shù)返回的數(shù)字沒有間斷，并且始終具有連續(xù)的排名。 
   整個(gè)查詢所用的排序順序確定了各行在結(jié)果中的顯示順序。這說明排名第一的行可以不是分區(qū)中的第一行。
參數(shù)： partition_by_clause > ：將 FROM 子句所生成的結(jié)果集劃分為數(shù)個(gè)將應(yīng)用 DENSE_RANK 函數(shù)的分區(qū)。 
    order_by_clause >：確定將 DENSE_RANK 值應(yīng)用于分區(qū)中各行的順序。
返回類型：bigint

示例：
/*以下示例返回各位置上產(chǎn)品數(shù)量的 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
*/

-------------------------------------------------------------------------------------------------------
將上面三個(gè)函數(shù)放在一起計(jì)算，更能明顯看出各個(gè)函數(shù)的功能。

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
--對(duì)一個(gè)列qty進(jìn)行的排序
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 行受影響)
*/ 

--對(duì)兩個(gè)列qty,orderid進(jìn)行的排序
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 行受影響)
*/