SQL 2005之四种排名函数

核心提示： 上述方法对于您只对行的一个特定页感兴趣的特定请求而言已经足够了，但是，SQL 2005之四种排名函数(6)，当用户发出多个请求时，该方法就不能满足需要了，并且指定一个表达式列表，以标识应该为其独立计算排序值的行组，因为该查询的每个调用都需要您对表进行完整扫描，以便计算行号

上述方法对于您只对行的一个特定页感兴趣的特定请求而言已经足够了。但是，当用户发出多个请求时，该方法就不能满足需要了，因为该查询的每个调用都需要您对表进行完整扫描，以便计算行号。当用户可能反复请求不同的页时，为了更有效地进行分页，请首先用所有基础表行（包括计算得到的行号）填充一个临时表，并且对包含这些行号的列进行索引：

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)

然后，对于所请求的每个页，发出以下查询：

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

只有属于预期页的行才会被扫描。

分段

可以在行组内部独立地计算排序值，而不是为作为一个组的所有表行计算排序值。为此，请使用 PARTITION BY 子句，并且指定一个表达式列表，以标识应该为其独立计算排序值的行组。例如，以下查询按照“score DESC, speaker”顺序单独分配每个 track 内部的行号：

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

以下为结果集：

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