从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 按班级分区、按学生姓名进行编号

为了理解SQL Server中排名函数的工作原理，我们来看一下查询优化器为查询生成的执行计划，如图9-4所示。

图9-4 为ROW_NUMBER( )生成的执行计划

由上图可以看出，为了计算排名，优化器首先按分区列排序，然后再对分区内行按ORDER BY子句指定的列排序。如果事先为表创建了符合该排序条件的索引，则会直接扫描该索引文件，不再进行排序。

“序列射影”运算符的工作是负责计算排名，“段”运算符用于确定分组边界。二者相互协调工作，来确定每一行的排名值。

“段”运算符在内存中会保留一行，用来与下一行的PARTITION BY列值进行比较。对于表中的第一行，“段”运算符自然会发送true信号。对于后面的行，直到PARTITIONBY列值有变化之前，会一直发送false信号。如果PARTITION BY列值发生了变化，说明已经到了下一个分区，“段”运算符会再次发送true信号。“序列射影”运算符在接收到true信号后，会重置排名值。<??http://www.2cto.com/kf/ware/vc/" target="_blank" class="keylink">vcD48cD7I57n7obDQ8sHQyeTTsKGx1MvL47f7vdPK1bW9tcTKx2ZhbHNl0MW6xaOsy/y74ci3yM+1scewyuTI69DQtcTFxdDy1rXKx7fxsrvNrNPayc/Su9DQo6zI57n7srvNrKOs1PKwtMXFw/u6r8r9y/nWuMq+tcS13dT2xcXD+9a1oaPX1Mi7o6zU2rjDyr7A/dbQo6zTydPaUk9XX05VTUJFUiggKbqvyv3Q6NKqzqrDv9K70NC13dT21rWho9LytMujrNXiuPbFxdDy1rWxyL3Psr3W6NTauMPKvsD91tDKx7K7tObU2rXEoaO1q8rHo6y21NPaz/FSQU5LKCApus1ERU5TRV9SQU5LKCApuq/K/aOs1NrWtNDQvMa7rtbQu7m74dPQwe3N4tK7uPahsLbOobHUy8vjt/ujrNPD09qxyL3PxcXQ8ta1yse38dPQseS7r6Os0tTIt7aoyse38bXd1PbFxcP71rWho7TLzsrM4s7Sw8fU2s/Cw+a7ubvh09C96cncoaM8L3A+PHA+yOe5+732vfbKx86qwcux4LrFo6zU8r/J0tTKocLUtfRQQVJUSVRJT04gQlnX077koaPA/cjno6zPwsPmtcTT777kzqqx7dbQy/nT0LXE0NC00zG/qsq8vfjQ0LHgusWhozwvcD48cD5TRUxFQ1QgQ2xhc3NJRCwgU3R1ZGVudE5hbWUsIEFjaGlldmVtZW50LDwvcD48cD4gICAgICBST1dfTlVNQkVSKCkgT1ZFUihPUkRFUiBCWSBTdHVkZW50TmFtZSkgQVMgUm93TnVtYmVyPC9wPjxwPkZST00gZGJvLlN0dWRlbnRzOzwvcD48aDM+OS4zLjIgUkFOS7rNREVOU0VfUkFOS7qvyv08L2gzPjxwPlJPV19OVU1CRVK6r8r908PT2rHgusWjrMv80+vFxcP7vt/T0LK7zay1xLjFxO6ho8D9yOejrNPJse05LTExv8nS1L+0s/ajrLDgvLYx1tC1xEdyYWNlus1BbmRyZXe1xLPJvKjP4M2so6y2vMrHOTm31qGjyOe5+8q508NST1dfTlVNQkVSuq/K/bHgusWjrNPQwb3W1rHgusW3vbC4v8m5qdGh1PGjutK71tbKx0dyYWNltdoxoaJBbmRyZXe12jKjrMHt0rvW1srHQW5kcmV3tdoxoaJHcmFjZbXaMqGj1eLL5Mi7trzKx9X9yLe1xKOsy/y+39PQsrvIt7ao0NShozwvcD48cD62+MXFw/vU8rK7zazBy6Osy/y+39PQyLe2qNDUo6zP4M2stcTFxdDy1rXX3MrHsbu31sXkz+DNrLXExcXD+9a1oaNHcmFjZbrNQW5kcmV31NrFxcP7tcTH6b/2z8K2vNOmtbHKx7XaMaOs0rK+zcrHztLDx7Ojy7W1xLKiwdC12jGho8THy/vDx8G9yMvWrrrztcTD+7TOysfKssO0xNijv8rHtdoyu7nKx7XaM8TYo7+008G9yMuyosHQtdoxtcS9x7bIvbKjrMv7w8fBvcjL1q6687XEw/u0ztOmtbHKx7XaMqOs1eLSssrHREVOU0VfUkFOS7qvyv21xMXFw/u3vcq9o7vHsMPm0tG+rdPQMrj2yMs5ObfWwcujrMv7w8e688PmtcTIy9OmtbHKx7XaM7j2uN+31tXfo6y009XiuPa9x7bIwO294qOsuvPD5rXEw/u0ztOmtbHKx7XaM6Os1eLSssrHUkFOSyggKbXExcXD+7e9yr2ho0RFTlNFX1JBTku6r8r9tcTFxcP7t73Kvc7Sw8ezxtauzqrD3LyvxcXD+6Os0vLOqsv8tcTD+7TO1q685MO709C85Lj0oaM8L3A+PHA+z8LD5rXE0+++5NHdyr7By1JBTku6zURFTlNFX1JBTku1xMXFw/u3vcq9o6yy6dGvveG5+8jnse05LTEyy/nKvqGjPC9wPjxwPlNFTEVDVCBDbGFzc0lELCBTdHVkZW50TmFtZSwgQWNoaWV2ZW1lbnQsPC9wPjxwPiAgICAgIFJBTksoKSBPVkVSKFBBUlRJVElPTiBCWSBDbGFzc0lEIE9SREVSIEJZIEFjaGlldmVtZW50IERFU0MpIEFTIFNvcnRSYW5rLDwvcD48cD4gICAgICBERU5TRV9SQU5LKCkgT1ZFUihQQVJUSVRJT04gQlkgQ2xhc3NJRCBPUkRFUiBCWSBBY2hpZXZlbWVudCBERVNDKSBBU1NvcnREZW5zZTwvcD48cD5GUk9NIGRiby5TdHVkZW50czsgPC9wPjxwPrHtOS0xMiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgILC0sOC8trrNv7zK1LPJvKi31rHwyrnTw1JBTku6zURFTlNFX1JBTkvFxcP7PC9wPjx0YWJsZSBib3JkZXI9"1" cellspacing="0" cellpadding="0">

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 分组结果

也可以先分区，再分组。例如，下面的语句将每个班级的成绩划分为高、低两组，查询结果如表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 按班级分区再按成绩分组结果