SQL Server 2005增加的排名函数

 

排名函数是SQL Server 2005增加的一个非常受欢迎的特性，它允许我们在结果集中返回和每行关联的值。表8-8描述了4个新的排名函数。

 

 

 

 

表8-8  排名函数

函    数	描    述
ROW_NUMBER	在结果集中为每行返回递增整数
RANK	和ROW_NUMBER相似，RANK为结果集中的每行递增值。主要的区别是如果行有重复值，它们会有相同的排名值
DENSE_RANK	DENSE_RANK和RANK几乎一样，只是DENSE_RANK不在排名值中返回间隔
NTILE	NTILE根据排序和可选的分区把结果集划分成指定数量的分组

下面4个技巧会演示这4个排名函数的使用。

8.7.1　使用递增行号

SQL Server 2005新的ROW_NUMBER在结果集中为每行返回递增的整数。ROW_NUMBER的语法如下：

第一个可选参数partition_by_clause允许你为分区列中的每个改变重置行号。第二个参数order_by_clause指定ROW_NUMBER应用到结果集的次序。

第一个示例返回结果集中间的6行，根据名字排序：

选择结果集中间从255到260的行

这个查询返回：

可选的partition_by_clause允许为分区列的每次修改重置行号。在这个示例中，结果根据Shelf来分区并且根据ProductID排序：

在返回的结果中，行号根据ProductID递增，但是每一次Shelf变化后，行号都会从1开始：

解析

在第一个示例中，我们使用ROW_NUMBER来根据产品名排列结果并且为每行增加递增值。在子查询的第三列中引用了ROW_NUMBER：

括号中的ORDER BY子句根据产品名对结果排序，它影响行返回的次序以及和每行关联的行号。结果集中的每一行都会有一个号码，每行都会加1。由于查询根据Name排列结果，第一个产品Adjustable Race的行号会是“1”。把这个查询作为子查询，这样ROW_NUMBER列就能在外部查询的WHERE子句中被引用，返回从255到260的行。

第二个查询演示了使用partition_by_clause参数。对于Shelf的每一次改变，行号都会从“1”开始。

有了SQL Server 2005的ROW_NUMBER函数，我们现在就可以不用像SQL Server 2000那样创建其他代码实现数据分页了（例如，显示25到50行）。

8.7.2　根据排名返回行

在这个技巧中，我会演示SQL Server 2005新的RANK函数，它和ROW_NUMBER相似，也为集合中的每一行增加值。RANK的语法如下：

主要的区别是如果行有相同的值存在，那么他们会获取相同的排名值，如本例演示的那样：

这个查询返回：

和ROW_NUMBER一样，OVER子句包含可选的partition_by_clause和必需的order_by_clause。order_ by_clause决定RANK值应用到每行的顺序，可选的partition_by_clause用于进一步划分排序分组，如下例：

这个查询根据销售人员的TerritoryID进行分区并返回SalesQuota的排名：

解析

RANK根据排序的列增加它的值，和ROWNUMBER的增加每行值不同，RANK会为排序值一致的行返回相同的值。

例如，在这个技巧中，查询指定了根据SalesQuota降序排序的RANK。由于两个SalesQuota值都等于280 000.00，它们都得到了排名7：

你也注意到了，下面的SalesQuota值排名是9（而不是8）。RANK函数没有使用第8个位置是因为两行是并列第七，那么下一个排名值是9。如果3行并列，那么下一个排名值是10，依此类推：

在第二个示例中，RANK根据TerritoryID分区，对于TerritoryID的每一次修改RANK值都会从“1”开始。

8.7.3　根据无间隔排名返回行

在这个技巧中，我会演示SQL Server 2005新的DENSE_RANK，它几乎和RANK一样，只是DENSE_RANK不返回有间隔的排名值：

这个查询返回：

解析

它的语法和使用与RANK相同，只是DENSE_RANK不在排名值中创建间隔。在这个技巧的示例中，由于两个SalesQuota是280 000.00，所以两个排名都是第7：

在7之后的DENSE_RANK值为8。

<script type=text/javascript> </script> <script type=text/javascript src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script> <script>window.google_render_ad();</script>

8.7.4　使用NTILE

NTILE根据排序和可选的分区把结果集分成指定数量的分组。语法和其他排名函数相似，只是它包含一个integer_expression：

integer_expression用于指定要将结果划分成的分组数。本例演示对Sales.SalePersonQuotaHis- tory表操作的NTILE排名函数：

这个查询返回：

解析

在这个示例中，结果集被划分成4个分组。结果根据SalesQuota排序（降序），并且指定了NTILE分组分配的次序。注意到，前面的两组1和2都有3行，而分组3和4都有2行。如果行数不能被分组数整除，那么前几个分组会比后面几个分组多一些行。否则，如果行数能被分组数整除，每一个分组会有相同数量的行。