摘自作者新書《鋒利的SQL》（第2版），網購京東：http://item.jd.com/11692900.html
從SQL Server2005開始，提供了4個排名函數，分别是：ROW_NUMBER、RANK、DENSE_RANK和NTILE。ROW_NUMBER用于按行進行編号，RANK和DENSE_RANK用于按指定順序排名，NTILE用于對資料進行分組。
對于排名函數而言，OVER子句中可以包含PARTITION BY和ORDER BY子句，其中，ORDER BY是必選的。因為對于排名而言，沒有順序的排名沒有任何意義。
本節我們将使用9.1節建立的Students表為例進行介紹。像Students表這樣的資料結構設計，相對于資料庫存儲而言是比較合理的，因為我們不可能為每個班級建立一個表，但确實又存在像為每個班級中的學生成績進行排序或為學生編号這樣的實際需求，SQL Server的視窗計算技術就有效解決了二者之間的沖突。
9.3.1 ROW_NUMBER函數
ROW_NUMBER傳回分區内行的序列号，每個分區的第一行從1開始。例如，下面的語句指定按ClassID進行分區，并按StudentName進行排序編号。查詢結果如表9-11所示。
SELECT ClassID, StudentName, Achievement,
ROW_NUMBER() OVER(PARTITION BY ClassID ORDER BY StudentName) ASRowNumber
FROM dbo.Students;
表9-11 按班級分區、按學生姓名進行編号
| ClassID | StudentName | Achievement | RowNumber |
| 1 | Andrew | 99.00 | 1 |
| 1 | Grace | 99.00 | 2 |
| 1 | Janet | 75.00 | 3 |
| 1 | Margaret | 89.00 | 4 |
| 2 | Michael | 72.00 | 1 |
| 2 | Robert | 91.00 | 2 |
| 2 | Steven | 86.00 | 3 |
| 3 | Ann | 94.00 | 1 |
| 3 | Ina | 80.00 | 2 |
| 3 | Ken | 92.00 | 3 |
| 3 | Laura | 75.00 | 4 |
為了了解SQL Server中排名函數的工作原理，我們來看一下查詢優化器為查詢生成的執行計劃，如圖9-4所示。
圖9-4 為ROW_NUMBER( )生成的執行計劃
由上圖可以看出，為了計算排名，優化器首先按分區列排序，然後再對分區内行按ORDER BY子句指定的列排序。如果事先為表建立了符合該排序條件的索引，則會直接掃描該索引檔案，不再進行排序。
“序列射影”運算符的工作是負責計算排名，“段”運算符用于确定分組邊界。二者互相協調工作，來确定每一行的排名值。
“段”運算符在記憶體中會保留一行，用來與下一行的PARTITION BY列值進行比較。對于表中的第一行，“段”運算符自然會發送true信号。對于後面的行，直到PARTITIONBY列值有變化之前，會一直發送false信号。如果PARTITION BY列值發生了變化，說明已經到了下一個分區，“段”運算符會再次發送true信号。“序列射影”運算符在接收到true信号後，會重置排名值。
如果“序列射影”運算符接收到的是false信号，它會确認目前輸入行的排序值是否不同于上一行，如果不同，則按排名函數所訓示的遞增排名值。自然，在該示例中，由于ROW_NUMBER( )函數需要為每一行遞增值。是以，這個排序值比較步驟在該示例中是不存在的。但是，對于像RANK( )和DENSE_RANK( )函數，在執行計劃中還會有另外一個“段”運算符，用于比較排序值是否有變化，以确定是否遞增排名值。此問題我們在下面還會有介紹。
如果僅僅是為了編号，則可以省略掉PARTITION BY子句。例如，下面的語句為表中所有的行從1開始進行編号。
SELECT ClassID, StudentName, Achievement,
ROW_NUMBER() OVER(ORDER BY StudentName) AS RowNumber
FROM dbo.Students;
9.3.2 RANK和DENSE_RANK函數
ROW_NUMBER函數用于編号，它與排名具有不同的概念。例如，由表9-11可以看出，班級1中的Grace和Andrew的成績相同，都是99分。如果使用ROW_NUMBER函數編号，有兩種編号方案可供選擇：一種是Grace第1、Andrew第2，另一種是Andrew第1、Grace第2。這雖然都是正确的，它具有不确定性。
而排名則不同了，它具有确定性，相同的排序值總是被配置設定相同的排名值。Grace和Andrew在排名的情況下都應當是第1，也就是我們常說的并列第1。那他們兩人之後的名次是什麼呢？是第2還是第3呢？從兩人并列第1的角度講，他們兩人之後的名次應當是第2，這也是DENSE_RANK函數的排名方式；前面已經有2個人99分了，他們後面的人應當是第3個高分者，從這個角度了解，後面的名次應當是第3，這也是RANK( )的排名方式。DENSE_RANK函數的排名方式我們稱之為密集排名，因為它的名次之間沒有間隔。
下面的語句示範了RANK和DENSE_RANK的排名方式，查詢結果如表9-12所示。
SELECT ClassID, StudentName, Achievement,
RANK() OVER(PARTITION BY ClassID ORDER BY Achievement DESC) AS SortRank,
DENSE_RANK() OVER(PARTITION BY ClassID ORDER BY Achievement DESC) ASSortDense
FROM dbo.Students;
表9-12 按班級和考試成績分别使用RANK和DENSE_RANK排名
| ClassID | StudentName | Achievement | SortRank | SortDense |
| 1 | Grace | 99.00 | 1 | 1 |
| 1 | Andrew | 99.00 | 1 | 1 |
| 1 | Margaret | 89.00 | 3 | 2 |
| 1 | Janet | 75.00 | 4 | 3 |
| 2 | Robert | 91.00 | 1 | 1 |
| 2 | Steven | 86.00 | 2 | 2 |
| 2 | Michael | 72.00 | 3 | 3 |
| 3 | Ann | 94.00 | 1 | 1 |
| 3 | Ken | 92.00 | 2 | 2 |
| 3 | Ina | 80.00 | 3 | 3 |
| 3 | Laura | 75.00 | 4 | 4 |
圖9-5是為語句生成的執行計劃，與ROW_NUMBER相比，執行計劃中多出了一個“段”運算符。右邊段的分組依據是ClassID，左邊段的分組依據是ClassID和Achievement，這是多出的“段”。右邊的“段”用于分區操作，在到達下一個分區時發送true信号，“序列射影”運算符會重置排名值。而左邊的“段”用于比較排序值是否有變化，如果有變化，則通知“序列射影”運算符遞增排名值，遞增方式則按RANK和DENSE_RANK函數的規則進行。
圖9-5 為RANK和DENSE_RANK生成的執行計劃
在SQL Server2005之前，也可以使用子查詢的方式實作排名計算。語句的原理就是查詢出比目前成績高的個數，再加上1，就是該成績的排名。例如，在第1個班級中，比99分高的成績為0，加上1後，該成績就是第1名。下面語句的執行結果與表9-12完全相同，但是由于對于每個成績都要執行兩次子查詢，在性能方面與RANK和DENSE_RANK函數相差很遠。
SELECT ClassID, StudentName, Achievement,
(SELECT COUNT(*) FROM dbo.Students AS S2
WHERE S2.ClassID = S1.ClassID AND S2.Achievement > S1.Achievement)+1AS SortRank,
(SELECT COUNT(DISTINCT achievement) FROM dbo.Students AS S2
WHERE S2.ClassID = S1.ClassID AND S2.Achievement > S1.Achievement)+1AS SortDense
FROM dbo.Students AS S1
ORDER BY ClassID, Achievement DESC;
9.3.3 NTILE函數
NTILE函數用于把行分發到指定數目的組中。各個組有編号，編号從1開始。對于每一個行，NTILE将傳回此行所屬的組的編号。
NTILE函數可以接受一個代表組數量的參數，分組的方式“均分”原則。例如，假設一個表有10行，需要分成2組，則每個組都會有5行。如果表有11行，需要分成3個組，這時候是無法均分的。它配置設定方法是先得到一個能夠整除的基組大小（11/3=3），每組應當配置設定3行，剩餘的2行（11-9）會被再次均分到前面的2組中。
例如，下面的語句指定将Students表按學生成績劃分為3個組，并且Students表恰好也是11行，分組結果如表9-13所示。
SELECT ClassID, StudentName, Achievement,
NTILE(3) OVER(ORDER BY Achievement DESC) AS Tile
FROM dbo.Students;
表9-13 分組結果
| ClassID | StudentName | Achievement | Tile |
| 1 | Grace | 99.00 | 1 |
| 1 | Andrew | 99.00 | 1 |
| 3 | Ann | 94.00 | 1 |
| 3 | Ken | 92.00 | 1 |
| 2 | Robert | 91.00 | 2 |
| 1 | Margaret | 89.00 | 2 |
| 2 | Steven | 86.00 | 2 |
| 3 | Ina | 80.00 | 2 |
| 3 | Laura | 75.00 | 3 |
| 1 | Janet | 75.00 | 3 |
| 2 | Michael | 72.00 | 3 |
也可以先分區，再分組。例如，下面的語句将每個班級的成績劃分為高、低兩組，查詢結果如表9-14所示。可以看出，包含4名學生的班級，每組是2人；包含3名學生的班級，第1組是2人，第2組是1人。
SELECT ClassID, StudentName, Achievement,
CASENTILE(2) OVER(PARTITION BY ClassID ORDER BY Achievement DESC)
WHEN 1 THEN '高'
WHEN 2 THEN '低'
ENDAS Tile
FROM dbo.Students;
表9-14 按班級分區再按成績分組結果
| ClassID | StudentName | Achievement | Tile |
| 1 | Grace | 99.00 | 高 |
| 1 | Andrew | 99.00 | 高 |
| 1 | Margaret | 89.00 | 低 |
| 1 | Janet | 75.00 | 低 |
| 2 | Robert | 91.00 | 高 |
| 2 | Steven | 86.00 | 高 |
| 2 | Michael | 72.00 | 低 |
| 3 | Ann | 94.00 | 高 |
| 3 | Ken | 92.00 | 高 |
| 3 | Ina | 80.00 | 低 |
| 3 | Laura | 75.00 | 低 |