原文轉(zhuǎn)自 ： http://www.cnblogs.com/xhyang110/archive/2009/10/27/1590448.html

?

排名函數(shù)是SQL Server2005新加的功能。在SQL Server2005中有如下四個(gè)排名函數(shù)：

　　1.row_number

　　2.rank

　　3.dense_rank

　　4.ntile　　

　　下面分別介紹一下這四個(gè)排名函數(shù)的功能及用法。在介紹之前假設(shè)有一個(gè)t_table表，表結(jié)構(gòu)與表中的數(shù)據(jù)如圖1所示：

　　圖1

　　其中field1字段的類型是int，field2字段的類型是varchar

　　一、row_number

　　row_number函數(shù)的用途是非常廣泛，這個(gè)函數(shù)的功能是為查詢出來的每一行記錄生成一個(gè)序號(hào)。row_number函數(shù)的用法如下面的SQL語句所示：

select row_number() over(order by field1) as row_number,* fromt_table

　　上面的SQL語句的查詢結(jié)果如圖2所示。

　　圖2

　　其中row_number列是由row_number函數(shù)生成的序號(hào)列。在使用row_number函數(shù)是要使用over子句選擇對(duì)某一列進(jìn)行排序，然后才能生成序號(hào)。

　　實(shí)際上，row_number函數(shù)生成序號(hào)的基本原理是先使用over子句中的排序語句對(duì)記錄進(jìn)行排序，然后按著這個(gè)順序生成序號(hào)。over子句中的order by子句與SQL語句中的order by子句沒有任何關(guān)系，這兩處的order by 可以完全不同，如下面的SQL語句所示

select row_number() over(order by field2 desc) as row_number,*from t_table order by field1 desc

　　上面的SQL語句的查詢結(jié)果如圖3所示。

　　圖3

　　我們可以使用row_number函數(shù)來實(shí)現(xiàn)查詢表中指定范圍的記錄，一般將其應(yīng)用到Web應(yīng)用程序的分頁功能上。下面的SQL語句可以查詢t_table表中第2條和第3條記錄：

with t_rowtable
as
(
　　select row_number() over(order by field1) as row_number,*from t_table
)
select * from t_rowtable where row_number>1 and row_number<4 order by field1

　　上面的SQL語句的查詢結(jié)果如圖4所示。

　　圖4

　　上面的SQL語句使用了CTE，關(guān)于CTE的介紹將讀者參閱《SQL Server2005雜談（1）：使用公用表表達(dá)式（CTE）簡(jiǎn)化嵌套SQL》。

　　另外要注意的是，如果將row_number函數(shù)用于分頁處理，over子句中的order by 與排序記錄的order by 應(yīng)相同，否則生成的序號(hào)可能不是有續(xù)的。

　　當(dāng)然，不使用row_number函數(shù)也可以實(shí)現(xiàn)查詢指定范圍的記錄，就是比較麻煩。一般的方法是使用顛倒Top來實(shí)現(xiàn)，例如，查詢t_table表中第2條和第3條記錄，可以先查出前3條記錄，然后將查詢出來的這三條記錄按倒序排序，再取前2條記錄，最后再將查出來的這2條記錄再按倒序排序，就是最終結(jié)果。SQL語句如下：

select * from(select top2 * from(select top3 * from t_table order by field1)a
order by field1 desc) b order by field1

　　上面的SQL語句查詢出來的結(jié)果如圖5所示。

　　圖5

　　這個(gè)查詢結(jié)果除了沒有序號(hào)列row_number，其他的與圖4所示的查詢結(jié)果完全一樣。

　　二、rank

　　rank函數(shù)考慮到了over子句中排序字段值相同的情況，為了更容易說明問題，在t_table表中再加一條記錄，如圖6所示。

　　圖6

　　在圖6所示的記錄中后三條記錄的field1字段值是相同的。如果使用rank函數(shù)來生成序號(hào)，這3條記錄的序號(hào)是相同的，而第4條記錄會(huì)根據(jù)當(dāng)前的記錄數(shù)生成序號(hào)，后面的記錄依此類推，也就是說，在這個(gè)例子中，第4條記錄的序號(hào)是4，而不是2。rank函數(shù)的使用方法與row_number函數(shù)完全相同，SQL語句如下：

select rank() over(order by field1),* from t_table order by field1

　　上面的SQL語句的查詢結(jié)果如圖7所示。

　　圖7

　　三、dense_rank

　　dense_rank函數(shù)的功能與rank函數(shù)類似，只是在生成序號(hào)時(shí)是連續(xù)的，而rank函數(shù)生成的序號(hào)有可能不連續(xù)。如上面的例子中如果使用dense_rank函數(shù)，第4條記錄的序號(hào)應(yīng)該是2，而不是4。如下面的SQL語句所示：

select dense_rank() over(order by field1),* from t_table order by field1

　　上面的SQL語句的查詢結(jié)果如圖8所示。

　　圖8

　　讀者可以比較圖7和圖8所示的查詢結(jié)果有什么不同

　　四、ntile

　　ntile函數(shù)可以對(duì)序號(hào)進(jìn)行分組處理。這就相當(dāng)于將查詢出來的記錄集放到指定長度的數(shù)組中，每一個(gè)數(shù)組元素存放一定數(shù)量的記錄。ntile函數(shù)為每條記錄生成的序號(hào)就是這條記錄所有的數(shù)組元素的索引（從1開始）。也可以將每一個(gè)分配記錄的數(shù)組元素稱為“桶”。ntile函數(shù)有一個(gè)參數(shù)，用來指定桶數(shù)。下面的SQL語句使用ntile函數(shù)對(duì)t_table表進(jìn)行了裝桶處理：

select ntile(4) over(order by field1)as bucket,* from t_table

　　上面的SQL語句的查詢結(jié)果如圖9所示。

　　圖9

　　由于t_table表的記錄總數(shù)是6，而上面的SQL語句中的ntile函數(shù)指定了桶數(shù)為4。

　　也許有的讀者會(huì)問這么一個(gè)問題，SQL Server2005怎么來決定某一桶應(yīng)該放多少記錄呢？可能t_table表中的記錄數(shù)有些少，那么我們假設(shè)t_table表中有59條記錄，而桶數(shù)是5，那么每一桶應(yīng)放多少記錄呢？

　　實(shí)際上通過兩個(gè)約定就可以產(chǎn)生一個(gè)算法來決定哪一個(gè)桶應(yīng)放多少記錄，這兩個(gè)約定如下：

　　1.編號(hào)小的桶放的記錄不能小于編號(hào)大的桶。也就是說，第1捅中的記錄數(shù)只能大于等于第2桶及以后的各桶中的記錄。

　2.所有桶中的記錄要么都相同，要么從某一個(gè)記錄較少的桶開始后面所有捅的記錄數(shù)都與該桶的記錄數(shù)相同。也就是說，如果有個(gè)桶，前三桶的記錄數(shù)都是10，而第4捅的記錄數(shù)是6，那么第5桶和第6桶的記錄數(shù)也必須是6。

　　根據(jù)上面的兩個(gè)約定，可以得出如下的算法：

　　//mod表示取余，div表示取整
　　if(記錄總數(shù)mod桶數(shù)==0)
　　{
　　　　recordCount=記錄總數(shù)div桶數(shù);
　　　　將每桶的記錄數(shù)都設(shè)為recordCount
　　}
　　else
　　{
　　　　recordCount1=記錄總數(shù)div桶數(shù)+1;
　　　　intn=1;　//　n表示桶中記錄數(shù)為recordCount1的最大桶數(shù)
　　　　m=recordCount1*n;
　　　　while(((記錄總數(shù)-m)　mod　(桶數(shù)-　n))　!=0)
　　　　{
　　　　　　n++;
　　　　　　m=recordCount1*n;
　　　　}
　　　　recordCount2=(記錄總數(shù)-m)div　(桶數(shù)-n);
　　　　將前n個(gè)桶的記錄數(shù)設(shè)為recordCount1
　　　　將n+1個(gè)至后面所有桶的記錄數(shù)設(shè)為recordCount2
　　}

　　根據(jù)上面的算法，如果記錄總數(shù)為59，桶數(shù)為5，則前4個(gè)桶的記錄數(shù)都是12，最后一個(gè)桶的記錄數(shù)是11。

　　如果記錄總數(shù)為53，桶數(shù)為5，則前3個(gè)桶的記錄數(shù)為11，后2個(gè)桶的記錄數(shù)為10。

　　就拿本例來說，記錄總數(shù)為6，桶數(shù)為4，則會(huì)算出recordCount1的值為2，在結(jié)束while循環(huán)后，會(huì)算出recordCount2的值是1，因此，前2個(gè)桶的記錄是2，后2個(gè)桶的記錄是1。

????? ROW_NUMBER、RANK、DENSE_RANK 和 NTILE，這些新函數(shù)使您可以有效地分析數(shù)據(jù)以及向查詢的結(jié)果行提供排序值。您可能發(fā)現(xiàn)這些新函數(shù)有用的典型方案包括：將連續(xù)整數(shù)分配給結(jié)果行，以便進(jìn)行表示、分頁、計(jì)分和繪制直方圖。

Speaker Statistics 方案

下面的 Speaker Statistics 方案將用來討論和演示不同的函數(shù)和它們的子句。大型計(jì)算會(huì)議包括三個(gè)議題：數(shù)據(jù)庫、開發(fā)和系統(tǒng)管理。十一位演講者在會(huì)議中發(fā)表演講，并且為他們的講話獲得 范圍為 1 到 9 的分?jǐn)?shù)。結(jié)果被總結(jié)并存儲(chǔ)在下面的 SpeakerStats 表中：

CREATE TABLE SpeakerStats(
??? speaker??????? VARCHAR(10) NOT NULL PRIMARY KEY
??? , track????????? VARCHAR(10) NOT NULL
??? , score????????? INT???????? NOT NULL
??? , pctfilledevals INT???????? NOT NULL
??? , numsessions??? INT???????? NOT NULL)
SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan',???? 'Sys', 3, 22, 4)
INSERT INTO SpeakerStats VALUES('Ron',???? 'Dev', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Kathy',?? 'Sys', 8, 27, 2)
INSERT INTO SpeakerStats VALUES('Suzanne', 'DB', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Joe',???? 'Dev', 6, 20, 2)
INSERT INTO SpeakerStats VALUES('Robert', 'Dev', 6, 28, 2)
INSERT INTO SpeakerStats VALUES('Mike',??? 'DB', 8, 20, 3)
INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)
INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)
INSERT INTO SpeakerStats VALUES('Brian',?? 'Sys', 7, 22, 3)
INSERT INTO SpeakerStats VALUES('Kevin',?? 'DB', 7, 25, 4)

每個(gè)演講者都在該表中具有一個(gè)行，其中含有該演講者的名字、議題、平均得分、填寫評(píng)價(jià)的與會(huì)者相對(duì)于參加會(huì)議的與會(huì)者數(shù)量的百分比以及該演講者發(fā)表演講的次數(shù)。本節(jié)演示如何使用新的排序函數(shù)分析演講者統(tǒng)計(jì)數(shù)據(jù)以生成有用的信息。

ROW_NUMBER

ROW_NUMBER 函數(shù)使您可以向查詢的結(jié)果行提供連續(xù)的整數(shù)值。例如，假設(shè)您要返回所有演講者的 speaker、track 和 score，同時(shí)按照 score 降序向結(jié)果行分配從 1 開始的連續(xù)值。以下查詢通過使用 ROW_NUMBER 函數(shù)并指定 OVER (ORDER BY score DESC) 生成所需的結(jié)果：

SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,?? speaker, track, scoreFROM SpeakerStatsORDER BY score DESC以下為結(jié)果集：

rownum speaker??? track????? score
------ ---------- ---------- -----------
1????? Jessica??? Dev??????? 9
2????? Ron??????? Dev??????? 9
3????? Suzanne??? DB???????? 9
4????? Kathy????? Sys??????? 8
5????? Michele??? Sys??????? 8
6????? Mike?????? DB???????? 8
7????? Kevin????? DB???????? 7
8????? Brian????? Sys??????? 7
9????? Joe??????? Dev??????? 6
10???? Robert???? Dev??????? 6
11???? Dan??????? Sys??????? 3
得 分最高的演講者獲得行號(hào) 1，得分最低的演講者獲得行號(hào) 11。ROW_NUMBER 總是按照請(qǐng)求的排序?yàn)椴煌男猩刹煌男刑?hào)。請(qǐng)注意，如果在 OVER() 選項(xiàng)中指定的 ORDER BY 列表不唯一，則結(jié)果是不確定的。這意味著該查詢具有一個(gè)以上正確的結(jié)果；在該查詢的不同調(diào)用中，可能獲得不同的結(jié)果。例如，在我們的示例中，有三個(gè)不同的 演講者獲得相同的最高得分 (9)：Jessica、Ron 和 Suzanne。由于 SQL Server 必須為不同的演講者分配不同的行號(hào)，因此您應(yīng)當(dāng)假設(shè)分別分配給 Jessica、Ron 和 Suzanne 的值 1、2 和 3 是按任意順序分配給這些演講者的。如果值 1、2 和 3 被分別分配給 Ron、Suzanne 和 Jessica，則結(jié)果應(yīng)該同樣正確。

如 果您指定一個(gè)唯一的 ORDER BY 列表，則結(jié)果總是確定的。例如，假設(shè)在演講者之間出現(xiàn)得分相同的情況時(shí)，您希望使用最高的 pctfilledevals 值來分出先后。如果值仍然相同，則使用最高的 numsessions 值來分出先后。最后，如果值仍然相同，則使用最低詞典順序 speaker 名字來分出先后。由于 ORDER BY 列表 — score、pctfilledevals、numsessions 和 speaker — 是唯一的，因此結(jié)果是確定的：

SELECT ROW_NUMBER() OVER(ORDER BY score DESC, pctfilledevals DESC,?????????????????????????? numsessions DESC, speaker) AS rownum,?? speaker, track, score, pctfilledevals, numsessionsFROM SpeakerStatsORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker以下為結(jié)果集：

rownum speaker??? track????? score?????? pctfilledevals numsessions
------ ---------- ---------- ----------- -------------- -----------
1????? Ron??????? Dev??????? 9?????????? 30???????????? 3
2????? Suzanne??? DB???????? 9?????????? 30???????????? 3
3????? Jessica??? Dev??????? 9?????????? 19???????????? 1
4????? Michele??? Sys??????? 8?????????? 31???????????? 4
5????? Kathy????? Sys??????? 8?????????? 27???????????? 2
6????? Mike?????? DB???????? 8?????????? 20???????????? 3
7????? Kevin????? DB???????? 7?????????? 25???????????? 4
8????? Brian????? Sys??????? 7?????????? 22???????????? 3
9????? Robert???? Dev??????? 6?????????? 28???????????? 2
10???? Joe??????? Dev??????? 6?????????? 20???????????? 2
11???? Dan??????? Sys??????? 3?????????? 22???????????? 4
新的排序函數(shù)的重要好處之一是它們的效率。SQL Server 的優(yōu)化程序只需要掃描數(shù)據(jù)一次，以便計(jì)算值。它完成該工作的方法是：使用在排序列上放置的索引的有序掃描，或者，如果未創(chuàng)建適當(dāng)?shù)乃饕瑒t掃描數(shù)據(jù)一次并對(duì)其進(jìn)行排序。

另一個(gè)好處是語法的簡(jiǎn)單性。為了讓您感受一下通過使用在 SQL Server 的較低版本中采用的基于集的方法來計(jì)算排序值是多么困難和低效，請(qǐng)考慮下面的 SQL Server 2000 查詢，它返回與上一個(gè)查詢相同的結(jié)果：
SELECT (SELECT COUNT(*)?? FROM SpeakerStats AS S2?
??????????? WHERE S2.score > S1.score???
??????????? OR (S2.score = S1.score???????? AND S2.pctfilledevals > S1.pctfilledevals)???
??????????? OR (S2.score = S1.score???????? AND S2.pctfilledevals = S1.pctfilledevals???????? AND S2.numsessions > S1.numsessions)???
??????????? OR (S2.score = S1.score???????? AND S2.pctfilledevals = S1.pctfilledevals???????? AND S2.numsessions = S1.numsessions???????? AND S2.speaker < S1.speaker)
??????????? ) + 1 AS rownum
??????????? , speaker, track, score, pctfilledevals, numsessions
??????????? FROM SpeakerStats AS S1
??????????? ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker

該查詢顯然比 SQL Server 2005 查詢復(fù)雜得多。此外，對(duì)于 SpeakerStats 表中的每個(gè)基礎(chǔ)行，SQL Server 都必須掃描該表的另一個(gè)實(shí)例中的所有匹配行。對(duì)于基礎(chǔ)表中的每個(gè)行，平均大約需要掃描該表的一半（最少）行。SQL Server 2005 查詢的性能惡化是線性的，而 SQL Server 2000 查詢的性能惡化是指數(shù)性的。即使是在相當(dāng)小的表中，性能差異也是顯著的。

行號(hào)的一個(gè)典型應(yīng)用是通過查詢結(jié)果 分頁 。給定頁大小（以行數(shù)為單位）和頁號(hào)，需要返回屬于給定頁的行。例如，假設(shè)您希望按照“score DESC, speaker”順序從 SpeakerStats 表中返回第二頁的行，并且假定頁大小為三行。下面的查詢首先按照指定的排序計(jì)算派生表 D 中的行數(shù)，然后只篩選行號(hào)為 4 到 6 的行（它們屬于第二頁）：

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
??????? speaker, track, score
????? FROM SpeakerStats) AS D
WHERE rownum BETWEEN 4 AND 6
ORDER BY score DESC, speaker
以下為結(jié)果集：

rownum speaker??? track????? score
------ ---------- ---------- -----------
4????? Kathy????? Sys??????? 8
5????? Michele??? Sys??????? 8
6????? Mike?????? DB???????? 8
用更一般的術(shù)語表達(dá)就是，給定 @pagenum 變量中的頁號(hào)和 @pagesize 變量中的頁大小，以下查詢返回屬于預(yù)期頁的行：
DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum
??????????????? ,speaker
??????????????? , track
??????????????? , score
??????????????? FROM SpeakerStats)
??????????????? AS DWHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
??????????????? ORDER BY score DESC, speaker

上述方法對(duì)于您只對(duì)行的一個(gè)特定頁感興趣的特定請(qǐng)求而言已經(jīng)足夠了。但是，當(dāng)用戶發(fā)出多個(gè)請(qǐng)求時(shí)，該方法就不能滿足需要了，因?yàn)樵摬樵兊拿總€(gè)調(diào)用都 需要您對(duì)表進(jìn)行完整掃描，以便計(jì)算行號(hào)。當(dāng)用戶可能反復(fù)請(qǐng)求不同的頁時(shí)，為了更有效地進(jìn)行分頁，請(qǐng)首先用所有基礎(chǔ)表行（包括計(jì)算得到的行號(hào)）填充一個(gè)臨時(shí) 表，并且對(duì)包含這些行號(hào)的列進(jìn)行索引：

SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, *
INTO #SpeakerStatsRN
FROM SpeakerStats
CREATE UNIQUE CLUSTERED INDEX idx_uc_rownum ON #SpeakerStatsRN(rownum)
然后，對(duì)于所請(qǐng)求的每個(gè)頁，發(fā)出以下查詢：

SELECT rownum, speaker, track, score
FROM #SpeakerStatsRN
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY score DESC, speaker
只有屬于預(yù)期頁的行才會(huì)被掃描。

分段
可以在行組內(nèi)部獨(dú)立地計(jì)算排序值，而不是為作為一個(gè)組的所有表行計(jì)算排序值。為此，請(qǐng)使用 PARTITION BY 子句，并且指定一個(gè)表達(dá)式列表，以標(biāo)識(shí)應(yīng)該為其獨(dú)立計(jì)算排序值的行組。例如，以下查詢按照“score DESC, speaker”順序單獨(dú)分配每個(gè) track 內(nèi)部的行號(hào)：

SELECT track,
ROW_NUMBER() OVER(
??? PARTITION BY track
??? ORDER BY score DESC, speaker) AS pos,
speaker, score
FROM SpeakerStats
ORDER BY track, score DESC, speaker
以下為結(jié)果集：

track????? pos speaker??? score
---------- --- ---------- -----------
DB???????? 1?? Suzanne??? 9
DB???????? 2?? Mike?????? 8
DB???????? 3?? Kevin????? 7
Dev??????? 1?? Jessica??? 9
Dev??????? 2?? Ron??????? 9
Dev??????? 3?? Joe??????? 6
Dev??????? 4?? Robert???? 6
Sys??????? 1?? Kathy????? 8
Sys??????? 2?? Michele??? 8
Sys??????? 3?? Brian????? 7
Sys??????? 4?? Dan??????? 3
在 PARTITION BY 子句中指定 track 列會(huì)使得為具有相同 track 的每個(gè)行組單獨(dú)計(jì)算行號(hào)。

RANK, DENSE_RANK

RANK 和 DENSE_RANK 函數(shù)非常類似于 ROW_NUMBER 函數(shù)，因?yàn)樗鼈円舶凑罩付ǖ呐判蛱峁┡判蛑担铱梢愿鶕?jù)需要在行組（分段）內(nèi)部提供。但是，與 ROW_NUMBER 不同的是，RANK 和 DENSE_RANK 向在排序列中具有相同值的行分配相同的排序。當(dāng) ORDER BY 列表不唯一，并且您不希望為在 ORDER BY 列表中具有相同值的行分配不同的排序時(shí)，RANK 和 DENSE_RANK 很有用。RANK 和 DENSE_RANK 的用途以及兩者之間的差異可以用示例進(jìn)行最好的解釋。以下查詢按照 score DESC 順序計(jì)算不同演講者的行號(hào)、排序和緊密排序值：

SELECT speaker, track, score,
ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
RANK() OVER(ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER(ORDER BY score DESC) AS drnk
FROM SpeakerStats
ORDER BY score DESC
以下為結(jié)果集：

speaker??? track????? score?????? rownum rnk drnk
---------- ---------- ----------- ------ --- ----
Jessica??? Dev??????? 9?????????? 1????? 1?? 1
Ron??????? Dev??????? 9?????????? 2????? 1?? 1
Suzanne??? DB???????? 9?????????? 3????? 1?? 1
Kathy????? Sys??????? 8?????????? 4????? 4?? 2
Michele??? Sys??????? 8?????????? 5????? 4?? 2
Mike?????? DB???????? 8?????????? 6????? 4?? 2
Kevin????? DB???????? 7?????????? 7????? 7?? 3
Brian????? Sys??????? 7?????????? 8????? 7?? 3
Joe??????? Dev??????? 6?????????? 9????? 9?? 4
Robert???? Dev??????? 6?????????? 10???? 9?? 4
Dan??????? Sys??????? 3?????????? 11???? 11 5
正 如前面討論的那樣，score 列不唯一，因此不同的演講者可能具有相同的得分。行號(hào)確實(shí)代表下降的 score 順序，但是具有相同得分的演講者仍然獲得不同的行號(hào)。但是請(qǐng)注意，在結(jié)果中，所有具有相同得分的演講者都獲得相同的排序和緊密排序值。換句話說，當(dāng) ORDER BY 列表不唯一時(shí)，ROW_NUMBER 是不確定的，而 RANK 和 DENSE_RANK 總是確定的。排序值和緊密排序值之間的差異在于，排序代表：具有較高得分的行號(hào)加 1，而緊密排序代表：具有明顯較高得分的行號(hào)加 1。從您迄今為止已經(jīng)了解的內(nèi)容中，您可以推導(dǎo)出當(dāng) ORDER BY 列表唯一時(shí)，ROW_NUMBER、RANK 和 DENSE_RANK 產(chǎn)生完全相同的值。

NTILE

NTILE 使您可以按照指定的順序，將查詢的結(jié)果行分散到指定數(shù)量的組 (tile) 中。每個(gè)行組都獲得不同的號(hào)碼：第一組為 1，第二組為 2，等等。您可以在函數(shù)名稱后面的括號(hào)中指定所請(qǐng)求的組號(hào)，在 OVER 選項(xiàng)的 ORDER BY 子句中指定所請(qǐng)求的排序。組中的行數(shù)被計(jì)算為 total_num_rows / num_groups。如果有余數(shù) n，則前面 n 個(gè)組獲得一個(gè)附加行。因此，可能不會(huì)所有組都獲得相等數(shù)量的行，但是組大小最大只可能相差一行。例如，以下查詢按照 score 降序?qū)⑷齻€(gè)組號(hào)分配給不同的 speaker 行：

SELECT speaker, track, score,
ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
NTILE(3) OVER(ORDER BY score DESC) AS tile
FROM SpeakerStats
ORDER BY score DESC
以下為結(jié)果集：

speaker??? track????? score?????? rownum tile
---------- ---------- ----------- ------ ----
Jessica??? Dev??????? 9?????????? 1????? 1
Ron??????? Dev??????? 9?????????? 2????? 1
Suzanne??? DB???????? 9?????????? 3????? 1
Kathy????? Sys??????? 8?????????? 4????? 1
Michele??? Sys??????? 8?????????? 5????? 2
Mike?????? DB???????? 8?????????? 6????? 2
Kevin????? DB???????? 7?????????? 7????? 2
Brian????? Sys??????? 7?????????? 8????? 2
Joe??????? Dev??????? 6?????????? 9????? 3
Robert???? Dev??????? 6?????????? 10???? 3
Dan??????? Sys??????? 3?????????? 11???? 3
在 SpeakerStats 表中有 11 位演講者。將 11 除以 3 得到組大小 3 和余數(shù) 2，這意味著前面 2 個(gè)組將獲得一個(gè)附加行（每個(gè)組中有 4 行），而第三個(gè)組則不會(huì)得到附加行（該組中有 3 行）。組號(hào)（tile 號(hào)）1 被分配給行 1 到 4，組號(hào) 2 被分配給行 5 到 8，組號(hào) 3 被分配給行 9 到 11。通過該信息可以生成直方圖，并且將項(xiàng)目均勻分布到每個(gè)梯級(jí)。在我們的示例中，第一個(gè)梯級(jí)表示具有最高得分的演講者，第二個(gè)梯級(jí)表示具有中等得分的演 講者，第三個(gè)梯級(jí)表示具有最低得分的演講者。可以使用 CASE 表達(dá)式為組號(hào)提供說明性的有意義的備選含義：

SELECT speaker, track, score,
CASE NTILE(3) OVER(ORDER BY score DESC)
??? WHEN 1 THEN 'High'
??? WHEN 2 THEN 'Medium'
??? WHEN 3 THEN 'Low'
END AS scorecategory
FROM SpeakerStats
ORDER BY track, speaker
以下為結(jié)果集：

speaker??? track????? score?????? scorecategory
---------- ---------- ----------- -------------
Kevin????? DB???????? 7?????????? Medium
Mike?????? DB???????? 8?????????? Medium
Suzanne??? DB???????? 9?????????? High
Jessica??? Dev??????? 9?????????? High
Joe??????? Dev??????? 6?????????? Low
Robert???? Dev??????? 6?????????? Low
Ron??????? Dev??????? 9?????????? High
Brian????? Sys??????? 7?????????? Medium
Dan??????? Sys??????? 3?????????? Low
Kathy????? Sys??????? 8?????????? High
Michele??? Sys??????? 8?????????? Medium

SQL2005四個(gè)排名函數(shù)（row_number、rank、dense_rank和ntile）的比較