SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法

--接上.
-------------------------------------------------------------------------------------
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 行受影响)
*/