SQL Server2005杂谈（3）：四个排名函数（row_n

本文为原创，如需转载，请注明作者和出处，谢谢！

上一篇： SQL Server2005杂谈（2）：公用表表达式（CTE）的递归调用

排名函数是 SQL Server2005 新加的功能。在 SQL Server2005 中有如下四个排名函数：

1.row_number

2.rank

3.dense_rank

4.ntile
下面分别介绍一下这四个排名函数的功能及用法。在介绍之前假设有一个t_table表，表结构与表中的数据如图1所示：

图1

其中 field1 字段的类型是 int ， field2 字段的类型是 varchar

一、 row_number

row_number 函数的用途是非常广泛，这个函数的功能是为查询出来的每一行记录生成一个序号。 row_number 函数的用法如下面的 SQL 语句所示：

select row_number() over ( order by field1) as row_number, * from t_table

上面的 SQL 语句的查询结果如图 2 所示。

图2

其中 row_number 列是由 row_number 函数生成的序号列。在使用 row_number 函数是要使用 over 子句选择对某一列进行排序，然后才能生成序号。

实际上， row_number 函数生成序号的基本原理是先使用 over 子句中的排序语句对记录进行排序，然后按着这个顺序生成序号。 over 子句中的 order by 子句与 SQL 语句中的 order by 子句没有任何关系，这两处的 order by 可以完全不同，如下面的 SQL 语句所示：

上面的 SQL 语句的查询结果如图 3 所示。

图3

with t_rowtable
as
(
select row_number() over ( order by field1) as row_number, * from t_table
)
select * from t_rowtable where row_number > 1 and row_number < 4 order by field1

图4

select * from ( select top 2 * from ( select top 3 * from t_table order by field1)a order by field1 desc )b order by field1

上面的 SQL 语句查询出来的结果如图 5 所示。

图5

这个查询结果除了没有序号列 row_number ，其他的与图 4 所示的查询结果完全一样。

二、 rank

rank 函数考虑到了 over 子句中排序字段值相同的情况，为了更容易说明问题，在 t_table 表中再加一条记录，如图 6 所示。

图6

select rank() over ( order by field1), * from t_table order by field1

上面的 SQL 语句的查询结果如图 7 所示。

图7

三、 dense_rank

dense_rank 函数的功能与 rank 函数类似，只是在生成序号时是连续的，而 rank 函数生成的序号有可能不连续。如上面的例子中如果使用 dense_rank 函数，第 4 条记录的序号应该是 2 ，而不是 4 。如下面的 SQL 语句所示：

select dense_rank() over ( order by field1), * from t_table order by field1

图8

读者可以比较图 7 和图 8 所示的查询结果有什么不同

四、ntile
ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引（从1开始）。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数，用来指定桶数。下面的SQL语句使用ntile函数对t_table表进行了装桶处理：

select ntile( 4 ) over ( order by field1) as bucket, * from t_table

上面的 SQL 语句的查询结果如图 9 所示。

图9

由于 t_table 表的记录总数是 6 ，而上面的 SQL 语句中的 ntile 函数指定了桶数为 4 。

也许有的读者会问这么一个问题， SQL Server2005 怎么来决定某一桶应该放多少记录呢？可能 t_table 表中的记录数有些少，那么我们假设 t_table 表中有 59 条记录，而桶数是 5 ，那么每一桶应放多少记录呢？

实际上通过两个约定就可以产生一个算法来决定哪一个桶应放多少记录，这两个约定如下：

1. 编号小的桶放的记录不能小于编号大的桶。也就是说，第 1 捅中的记录数只能大于等于第 2 桶及以后的各桶中的记录。

2. 所有桶中的记录要么都相同，要么从某一个记录较少的桶开始后面所有捅的记录数都与该桶的记录数相同。也就是说，如果有个桶，前三桶的记录数都是 10 ，而第 4 捅的记录数是 6 ，那么第 5 桶和第 6 桶的记录数也必须是 6 。

根据上面的两个约定，可以得出如下的算法：

// mod表示取余，div表示取整
if (记录总数mod桶数 == 0 )
{
recordCount = 记录总数div桶数;
将每桶的记录数都设为recordCount
}
else
{
recordCount1 = 记录总数div桶数 + 1 ;
int n = 1 ; // n表示桶中记录数为recordCount1的最大桶数
m = recordCount1 * n;
while (((记录总数 - m)mod(桶数 - n)) != 0 )
{
n ++ ;
m = recordCount1 * n;
}
recordCount2 = (记录总数 - m)div(桶数 - n);
将前n个桶的记录数设为recordCount1
将n + 1个至后面所有桶的记录数设为recordCount2
}

根据上面的算法，如果记录总数为 59 ，桶数为 5 ，则前 4 个桶的记录数都是 12 ，最后一个桶的记录数是 11 。

如果记录总数为 53 ，桶数为 5 ，则前 3 个桶的记录数为 11 ，后 2 个桶的记录数为 10 。

就拿本例来说，记录总数为 6 ，桶数为 4 ，则会算出 recordCount1 的值为 2 ，在结束 while 循环后，会算出 recordCount2 的值是 1 ，因此，前 2 个桶的记录是 2 ，后 2 个桶的记录是 1 。

下一篇： SQL Server2005杂谈（4）：在SQL Server2005中按列连接字符串的三种方法

国内最棒的Google Android技术社区（eoeandroid），欢迎访问！

《银河系列原创教程》发布

《Java Web开发速学宝典》出版，欢迎定购

SQL Server2005杂谈（3）：四个排名函数（row_number、rank、dense_rank和ntile）的比较

更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

2元

5元

10元

20元

自定义