SQL server排序函数细说

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

　1. row_number

　　2. rank

　　3. dense_rank

　　4. ntile

一、row_number

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

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

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

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

　　select row_number() over(order by field2 desc) as row_number,* fromt_table order by field1 desc

　　我们可以使用row_number函数来实现查询表中指定范围的记录，一般将其应用到Web应用程序的分页功能上。下面的SQL语句可以查询t_table表中第2条和第3条记录：

上面的SQL语句使用了CTE，关于CTE的介绍将读者参阅《SQL Server2005杂谈(1)：使用公用表表达式(CTE)简化嵌套SQL》。

　　另外要注意的是，如果将row_number函数用于分页处理，over子句中的order by 与排序记录的order by应相同，否则生成的序号可能不是有续的。

　　当然，不使用row_number函数也可以实现查询指定范围的记录，就是比较麻烦。一般的方法是使用颠倒Top来实现，例如，查询t_table表中第2条和第3条记录，可以先查出前3条记录，然后将查询出来的这三条记录按倒序排序，再取前2条记录，最后再将查出来的这2条记录再按倒序排序，就是最终结果。SQL语句如下：

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

　　这个查询结果除了没有序号列row_number

二、rank

　　rank函数考虑到了over子句中排序字段值相同的情况

　　在图6所示的记录中后三条记录的field1字段值是相同的。如果使用rank函数来生成序号，这3条记录的序号是相同的，而第4条记录会根据当前的记录 数生成序号，后面的记录依此类推，也就是说，在这个例子中，第4条记录的序号是4，而不是2。rank函数 的使用方法与row_number函数完全相 同，SQL语句如下：

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

三、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

四、ntile

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

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

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

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

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

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

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

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

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

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

 

另解:__________________________________________

下面是对这4个函数的解释：

　　RANK()

   RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

　　返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。

　　如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。

　　例如，如果两位销售员具有相同的SalesYTD值，则他们将并列第一。由于已有两行排名在前，所以具有下一个最大SalesYTD的销售人员将排名第三。

　　因此，RANK函数并不总返回连续整数。

　　DENSE_RANK()

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause > )

　　返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。

　　如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。

　　例如，如果两位顶尖销售员具有相同的 SalesYTD值，则他们将并列第一。接下来 SalesYTD最高的销售人员排名第二。该排名等于该行之前的所有行数加一。

　　因此，DENSE_RANK函数返回的数字没有间断，并且始终具有连续的排名。

　　ROW_NUMBER()

ROW_NUMBER ( )

    OVER ( [PARTITION BY value_expression , ... [ n ] ] order_by_clause )

 

　　回结果集分区内行的序列号，每个分区的第一行从 1开始。

　　ORDER BY子句可确定在特定分区中为行分配唯一 ROW_NUMBER的顺序。

　　NTILE()

NTILE (integer_expression) OVER ( [ <partition_by_clause> ]< order_by_clause > )

 

　　将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE将返回此行所属的组的编号。

　　如果分区的行数不能被 integer_expression整除，则将导致一个成员有两种大小不同的组。按照 OVER子句指定的顺序，较大的组排在较小的组前面。

 

 

 

 

 

 

 

 

 

droptable dbo.wzg_test

createtable dbo.wzg_test(

  id int

  ,name varchar(10)

  ,grade decimal

)

declare @iint = 1

declare @jint = 60

while @i<= 20

begin

   if @i%5 = 0

       set @j = @j+10

      insert into dbo.wzg_testvalues(@i,'wzg'+convert(varchar(10),@i),@j)

      set @i = @i+ 1

end

 

 

selectrow_number() over ( orderby id asc)as row_number,*

from dbo.wzg_test

orderby id asc

 

 

selectrow_number() over (partitionby grade  orderby id asc)as  row_number,*

from dbo.wzg_test

 

 

 

selectrank() over (orderby grade asc)as rank,*

from dbo.wzg_test

orderby id asc

 

 

selectdense_rank() over (orderby grade asc)as dense_rank,*

from dbo.wzg_test

orderby id asc

 

 

selectntile(3)over (orderby grade asc)as ntile,*

from dbo.wzg_test

orderby id asc