好用的排名函數~ROW_NUMBER(),RANK(),DENSE_RANK() 三兄弟

排名函數三兄弟，一看名字就知道，都是為了排名而生！但是各自有各自的特色！以下一個例子說明問題！(以下慄子沒有使用Partition By 的關鍵字，整個結果集進行排序)

RANK 每個值一個排名，同樣的值排同樣的位置，如第一名有2個，下一個值就要排第三，如此類推，表現如下麵的 RandNr 列

DENSE_RANK 每個值一個排名，跟Rank 不一致的地方在於它不跳號，會1,2,3 那樣排下來

ROW_NUMBER 每行一個排序值，遇到相同的排序條件的時候，按照順序給值，對應表現如下麵RowNr 列

;WITH CTE1(ID,Col1) AS
(
    SELECT 1 ,'AA'
    UNION ALL
    SELECT 1 ,'AA'
    UNION ALL
    SELECT 2 ,'BB'
    UNION ALL
    SELECT 3 ,'CC'
    UNION ALL
    SELECT 3 ,'CC'
    UNION ALL
    SELECT 4 ,'DD'
    UNION ALL
    SELECT 5 ,'EE'
)
SELECT RANK() OVER (ORDER BY ID) AS RankNr,
        DENSE_RANK() OVER (ORDER BY ID) AS DenseNr,
        ROW_NUMBER() OVER (ORDER BY ID) AS RowNr,
        *
    FROM CTE1


RankNr               DenseNr              RowNr                ID          Col1
-------------------- -------------------- -------------------- ----------- ----
1                    1                    1                    1           AA
1                    1                    2                    1           AA
3                    2                    3                    2           BB
4                    3                    4                    3           CC
4                    3                    5                    3           CC
6                    4                    6                    4           DD
7                    5                    7                    5           EE

然後其實說起排名函數，over 子句的作用也是相當關鍵的。

Over 子句後面的內容基本如下  

Over(

Partition By AAA,BBB --表示按照AAA,BBB進行分組，每個分組從1開始計數，如果忽略 Partition By 關鍵字，就當整個結果集作為一個分組來排序

Order by CCC asc,DDD desc --表示按照ccc,ddd 的執行排序賦予排序值，如果沒有特定的排序順序怎麼辦呢？ 可以使用  (select 1) 或者用 newid() 這個就用於隨機排序用的

)

三兄弟講完了~扯一下其它方面的

然而Over 子句還有一個更有用的用法，當使用視窗聚合函數(不是排序函數了) 的時候。Over 子句除了可以指定分組之外(這個貌似是2012之後的版本才支持，2012之前的版本只支持結果集的全部聚合)，

比方說我還是拿回之前生成了500行數據的測試表(數據沒有貼完整了)，有時候做對比和統計還是相當有用的喲~~~

SELECT ID,
        SUM(ID) OVER (ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Nr1,    --從首行累加到當前行
        SUM(ID) OVER (ORDER BY (SELECT 1) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS Nr2,            --前一行和當前行求和
        SUM(ID) OVER (ORDER BY (SELECT 1) ROWS BETWEEN CURRENT ROW AND  1 FOLLOWING ) AS Nr3        --後一行和當前行求和
    FROM dbo.Tmp123


ID          Nr1         Nr2         Nr3
----------- ----------- ----------- -----------
1           1           1           3
2           3           3           5
3           6           5           7
4           10          7           9
5           15          9           11
6           21          11          13
7           28          13          15
8           36          15          17
9           45          17          19
10          55          19          21
11          66          21          23
12          78          23          25
13          91          25          27
14          105         27          29