下面通過(guò)具體的方案將用來(lái)討論和演示不同的函數(shù)和它們的子句。

十一位演講者在會(huì)議中發(fā)表演講，并且為他們的講話(huà)獲得范圍為 1 到 9 的分?jǐn)?shù)。結(jié)果被總結(jié)并存儲(chǔ)在下面的 SpeakerStats 表中： 

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

1、ROW_NUMBER（）函數(shù)

返回結(jié)果集分區(qū)內(nèi)行的序列號(hào)，每個(gè)分區(qū)的第一行從 1 開(kāi)始。一般與OVER連用。
例如，假設(shè)您要返回所有演講者的 speaker、track 和 score，同時(shí)按照 score 降序向結(jié)果行分配從 1 開(kāi)始的連續(xù)值。以下查詢(xún)通過(guò)使用 ROW_NUMBER 函數(shù)并指定 OVER (ORDER BY score DESC) 生成所需的結(jié)果：    

Code
Code
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum, speaker, track, score
FROM SpeakerStats

rownum speaker    trackscore
------ ---------- ---------- -----------
1Jessica     Dev  9
2Ron   Dev  9
3Suzanne   DB   9
4Kathy     Sys   8
5Michele   Sys   8
6Mike DB    8
7KevinDB    7
8BrianSys   7
9Joe 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é)果是不確定的。這意味著該查詢(xún)具有一個(gè)以上正確的結(jié)果；在該查詢(xún)的不同調(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 值來(lái)分出先后。如果值仍然相同，則使用最高的 numsessions 值來(lái)分出先后。最后，如果值仍然相同，則使用最低詞典順序 speaker 名字來(lái)分出先后。由于 ORDER BY 列表 — score、pctfilledevals、numsessions 和 speaker — 是唯一的，因此結(jié)果是確定的：

Code

SELECT ROW_NUMBER() OVER(ORDER BY score DESC, pctfilledevals DESC,  numsessions DESC, speaker) AS rownum,   speaker, track, score, pctfilledevals, numsessions
FROM SpeakerStats

rownum speaker    trackscore pctfilledevals numsessions
------ ---------- ---------- ----------- -------------- -----------
1    Ron    Dev  9   30 3
2    Suzanne    DB   9   30 3
3    JessicaDev 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    RobertDev  6   28 2
10  Joe    Dev  6   20 2
11  Dan   Sys   3  22 4

本節(jié)所講到排序函數(shù)的重要好處之一是它們的效率。SQL Server 的優(yōu)化程序只需要掃描數(shù)據(jù)一次，以便計(jì)算值。它完成該工作的方法是：使用在排序列上放置的索引的有序掃描，或者，如果未創(chuàng)建適當(dāng)?shù)乃饕瑒t掃描數(shù)據(jù)一次并對(duì)其進(jìn)行排序。

另一個(gè)好處是語(yǔ)法的簡(jiǎn)單性。為了讓您感受一下通過(guò)使用在 SQL Server 的較低版本中采用的基于集的方法來(lái)計(jì)算排序值是多么困難和低效，請(qǐng)考慮下面的 SQL Server 2000 查詢(xún)，它返回與上一個(gè)查詢(xún)相同的結(jié)果：

Code
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.numsessios > 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

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

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

Code
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

以下為結(jié)果集：

rownum speaker    trackscore
------ ---------- ---------- -----------
4KathySys  8
5Michele    Sys  8
6Mike  DB  8

用更一般的術(shù)語(yǔ)表達(dá)就是，給定 @pagenum 變量中的頁(yè)號(hào)和 @pagesize 變量中的頁(yè)大小，以下查詢(xún)返回屬于預(yù)期頁(yè)的行：

Code
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 D 
 WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize

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

Code
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è)頁(yè)，發(fā)出以下查詢(xún)：

DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT rownum, speaker, track, score
FROM #SpeakerStatsRN
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY score DESC, speaker

只有屬于預(yù)期頁(yè)的行才會(huì)被掃描。

分段

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

Code
SELECT track,
ROW_NUMBER() OVER(
    PARTITION BY track
    ORDER BY score DESC, speaker) AS pos,speaker, score
FROM SpeakerStats

以下為結(jié)果集：

trackpos speaker    score
---------- --- ---------- -----------
DB   1   Suzanne    9
DB   2   Mike 8
DB   3   Kevin7
Dev  1   Jessica    9
Dev  2   Ron  9
Dev  3   Joe  6
Dev  4   Robert     6
Sys  1   Kathy8
Sys  2   Michele    8
Sys  3   Brian7
Sys  4   Dan  3

在 PARTITION BY 子句中指定 track 列會(huì)使得為具有相同 track 的每個(gè)行組單獨(dú)計(jì)算行號(hào)。

#p#

2、RANK, DENSE_RANK

RANK 和 DENSE_RANK 函數(shù)非常類(lèi)似于 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)行最好的解釋。以下查詢(xún)按照 score DESC 順序計(jì)算不同演講者的行號(hào)、排序和緊密排序值：

Code
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

speaker    trackscore    rownum rnk drnk
---------- ---------- ----------- ------ --- ----
Jessica    Dev     9     1     1   1
Ron  Dev     9     21   1
Suzanne    DB    9     31   1
KathySys    8     44   2
Michele    Sys   854   2
Mike DB     864   2
KevinDB     777   3
BrianSys    787   3
Joe  Dev    699   4
Robert     Dev   6     10     9   4
Dan  Sys   311    11  5

正如前面討論的那樣，score 列不唯一，因此不同的演講者可能具有相同的得分。行號(hào)確實(shí)代表下降的 score 順序，但是具有相同得分的演講者仍然獲得不同的行號(hào)。但是請(qǐng)注意，在結(jié)果中，所有具有相同得分的演講者都獲得相同的排序和緊密排序值。換句話(huà)說(shuō)，當(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)生完全相同的值。

3、NTILE

NTILE 使您可以按照指定的順序，將查詢(xún)的結(jié)果行分散到指定數(shù)量的組 (tile) 中。每個(gè)行組都獲得不同的號(hào)碼：第一組為 1，第二組為 2，等等。您可以在函數(shù)名稱(chēng)后面的括號(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ù)量的行，但是組大小最大只可能相差一行。例如，以下查詢(xún)按照 score 降序?qū)⑷齻€(gè)組號(hào)分配給不同的 speaker 行：

Code
SELECT speaker, track, score,
ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
NTILE(3) OVER(ORDER BY score DESC) AS tile
FROM SpeakerStats

以下為結(jié)果集：

speaker    trackscore rownum tile
---------- ---------- ----------- ------ ----
Jessica    Dev    9     11
Ron  Dev    9     21
Suzanne    DB   9     31
KathySys   8     41
Michele    Sys   8     52
Mike DB     8     62
KevinDB     7     72
BrianSys    7     82
Joe  Dev    6     93
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。通過(guò)該信息可以生成直方圖，并且將項(xiàng)目均勻分布到每個(gè)梯級(jí)。在我們的示例中，第一個(gè)梯級(jí)表示具有最高得分的演講者，第二個(gè)梯級(jí)表示具有中等得分的演講者，第三個(gè)梯級(jí)表示具有最低得分的演講者。可以使用 CASE 表達(dá)式為組號(hào)提供說(shuō)明性的有意義的備選含義：

Code
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

以下為結(jié)果集：

speaker    trackscore scorecategory
---------- ---------- ----------- -------------
KevinDB   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
BrianSys 7     Medium
Dan  Sys3     Low
KathySys8     High
Michele    Sys8     Medium

【編輯推薦】

1. SQL Server數(shù)據(jù)庫(kù)內(nèi)容替換方法
2. 有效使用SQL Server的自動(dòng)管理功能
3. 在SQL Server中創(chuàng)建全局臨時(shí)表技巧