下面通过具体的方案将用来讨论和演示不同的函数和它们的子句。
十一位演讲者在会议中发表演讲，并且为他们的讲话获得范围为 1 到 9 的分数。结果被总结并存储在下面的 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)
每个演讲者都在该表中具有一个行，其中含有该演讲者的名字、议题、平均得分、填写评价的与会者相对于参加会议的与会者数量的百分比以及该演讲者发表演讲的次数。本节演示如何使用新的排序函数分析演讲者统计数据以生成有用的信息。
1、ROW_NUMBER（）函数
返回结果集分区内行的序列号，每个分区的第一行从 1 开始。一般与OVER连用。
例如，假设您要返回所有演讲者的 speaker、track 和 score，同时按照 score 降序向结果行分配从 1 开始的连续值。以下查询通过使用 ROW_NUMBER 函数并指定 OVER (ORDER BY score DESC) 生成所需的结果：
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
得分最高的演讲者获得行号 1，得分最低的演讲者获得行号 11。ROW_NUMBER 总是按照请求的排序为不同的行生成不同的行号。请注意，如果在 OVER() 选项中指定的 ORDER BY 列表不唯一，则结果是不确定的。这意味着该查询具有一个以上正确的结果；在该查询的不同调用中，可能获得不同的结果。例如，在我们的示例中，有三个不同的演讲者获得相同的最高得分 (9)：Jessica、Ron 和 Suzanne。由于 SQL（SQL Server培训 mySQL培训 ） Server 必须为不同的演讲者分配不同的行号，因此您应当假设分别分配给 Jessica、Ron 和 Suzanne 的值 1、2 和 3 是按任意顺序分配给这些演讲者的。如果值 1、2 和 3 被分别分配给 Ron、Suzanne 和 Jessica，则结果应该同样正确。
如果您指定一个唯一的 ORDER BY 列表，则结果总是确定的。例如，假设在演讲者之间出现得分相同的情况时，您希望使用最高的 pctfilledevals 值来分出先后。如果值仍然相同，则使用最高的 numsessions 值来分出先后。最后，如果值仍然相同，则使用最低词典顺序 speaker 名字来分出先后。由于 ORDER BY 列表 ― score、pctfilledevals、numsessions 和 speaker ― 是唯一的，因此结果是确定的：
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
本节所讲到排序函数的重要好处之一是它们的效率。SQL Server 的优化程序只需要扫描数据一次，以便计算值。它完成该工作的方法是：使用在排序列上放置的索引的有序扫描，或者，如果未创建适当的索引，则扫描数据一次并对其进行排序。
另一个好处是语法的简单性。为了让您感受一下通过使用在 SQL Server 的较低版本中采用的基于集的方法来计算排序值是多么困难和低效，请考虑下面的 SQL Server 2000 查询，它返回与上一个查询相同的结果：
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
该查询显然比 SQL Server 2005 查询复杂得多。此外，对于 SpeakerStats 表中的每个基础行，SQL Server 都必须扫描该表的另一个实例中的所有匹配行。对于基础表中的每个行，平均大约需要扫描该表的一半（最少）行。SQL Server 2005 查询的性能恶化是线性的，而 SQL Server 2000 查询的性能恶化是指数性的。即使是在相当小的表中，性能差异也是显著的。
行号的一个典型应用是通过查询结果分页。给定页大小（以行数为单位）和页号，需要返回属于给定页的行。例如，假设您希望按照“score DESC, speaker”顺序从 SpeakerStats 表中返回第二页的行，并且假定页大小为三行。下面的查询首先按照指定的排序计算派生表 D 中的行数，然后只筛选行号为 4 到 6 的行（它们属于第二页）：
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
以下为结果集：
rownum speaker trackscore
------ ---------- ---------- -----------
4KathySys 8
5Michele Sys 8
6Mike DB 8
用更一般的术语表达就是，给定 @pagenum 变量中的页号和 @pagesize 变量中的页大小，以下查询返回属于预期页的行：
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
上述方法对于您只对行的一个特定页感兴趣的特定请求而言已经足够了。但是，当用户发出多个请求时，该方法就不能满足需要了，因为该查询的每个调用都需要您对表进行完整扫描，以便计算行号。当用户可能反复请求不同的页时，为了更有效地进行分页，请首先用所有基础表行（包括计算得到的行号）填充一个临时表，并且对包含这些行号的列进行索引：
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)
然后，对于所请求的每个页，发出以下查询：
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 内部的行号：
Code
SELECT track,
ROW_NUMBER() OVER(
PARTITION BY track
ORDER BY score DESC, speaker) AS pos,speaker, score
FROM SpeakerStats
以下为结果集：
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 列会使得为具有相同 track 的每个行组单独计算行号。
共2页: 1 下一页
【内容导航】
第 1 页：1、ROW_NUMBER（）函数 第 2 页：RANK, DENSE_RANK和NTILE
12下一页 |
