SQL Server 中的四種排序函數比较

先做一下總結：

    Row_Number():  不可並列，排序序號連續（可支持不排序，只加一個行號，這對分頁查詢很有作用）

    Rank():               可並列，排序序號不連續

    Dense_Rank():   可並列，排序序號連續

    nTitle(N):             先排序，再將結果分為N組

 

上面四個函數都支持先按某一字段分區，再在各個分區內分別排序。

再上一個實例：

源數據表S：

SNO        AGE    SEX
--------     ------    ----
09032501   19     男  
09032502   26     男  
09032503   21     女  
09032504   22     男  
09032505   21     女  
09032506   21     女  
09032507   25     女  
09032508   21     男  
09032509   23     女  
09032510   22     女  
09032511   19     女  
09032512   25     男  
09032513   23     男  
09032514   24     女  
09032515   21     男  
09032516   13     男  
09032517   19     女  
09032518   21     女  
09032519   23     女  
09032520   21     女 

 

腳本一（不用 partition 分區）：

select    SNO,AGE,SEX,    row_number() over(order by age) [row_number],    Rank() over(order by age) [Rank],    dense_rank() over(order by age) [dense_rank],    ntile(5) over(order by age) [ntile]from S

結果：

SNO      AGE    SEX  row_number    Rank                 dense_rank           ntile
-------- ------ ---- -------------------- -------------------- -------------------- --------------------
09032516 13     男                1                    1                    1                    1
09032517 19     女                2                    2                    2                    1
09032511 19     女                3                    2                    2                    1
09032501 19     男                4                    2                    2                    1
09032503 21     女                5                    5                    3                    2
09032505 21     女                6                    5                    3                    2
09032506 21     女                7                    5                    3                    2
09032508 21     男                8                    5                    3                    2
09032518 21     女                9                    5                    3                    3
09032515 21     男                10                  5                    3                    3
09032520 21     女                11                  5                    3                    3
09032510 22     女                12                  12                  4                    3
09032504 22     男                13                  12                  4                    4
09032509 23     女                14                  14                  5                    4
09032519 23     女                15                  14                  5                    4
09032513 23     男                16                  14                  5                    4
09032514 24     女                17                  17                  6                    5
09032512 25     男                18                  18                  7                    5
09032507 25     女                19                  18                  7                    5
09032502 26     男                20                  20                   8                   5

(20 row(s) affected)

 

腳本二（用 partition 分區）：

select    SNO,AGE,SEX,    row_number() over(partition by sex order by age) [row_number],    Rank() over(partition by sex order by age) [Rank],    dense_rank() over(partition by sex order by age) [dense_rank],    ntile(5) over(partition by sex order by age) [ntile]from S

結果：

SNO      AGE    SEX  row_number    Rank                 dense_rank           ntile
-------- ------ ---- -------------------- -------------------- -------------------- --------------------
09032516 13     男                    1                    1                    1                    1
09032501 19     男                    2                    2                    2                    1
09032508 21     男                    3                    3                    3                    2
09032515 21     男                    4                    3                    3                    2
09032504 22     男                    5                    5                    4                    3
09032513 23     男                    6                    6                    5                    3
09032512 25     男                    7                    7                    6                    4
09032502 26     男                    8                    8                    7                    5
09032511 19     女                    1                    1                    1                    1
09032517 19     女                    2                    1                    1                    1
09032518 21     女                    3                    3                    2                    1
09032503 21     女                    4                    3                    2                    2
09032505 21     女                    5                    3                    2                    2
09032506 21     女                    6                    3                    2                    2
09032520 21     女                    7                    3                    2                    3
09032510 22     女                    8                    8                    3                    3
09032519 23     女                    9                    9                    4                    4
09032509 23     女                    10                  9                    4                    4
09032514 24     女                    11                  11                   5                   5
09032507 25     女                    12                  12                   6                   5

(20 row(s) affected)