SQL Server 排名函数

内容摘要: Transact-SQL提供了4个排名函数: RANK(),DENSE_RANK(),ROW_NUMBER(),NTILE(),下文是对这4个函数进行的解释。


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

  RANK()

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

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

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

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

  DENSE_RANK()

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

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

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

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

  ROW_NUMBER()

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

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

  NTILE()

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

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

  例如,如果总行数是 53,组数是 5,则前三个组每组包含 11 行,其余两个组每组包含 10 行。

  另一方面,如果总行数可被组数整除,则行数将在组之间平均分布。

 例如,如果总行数为 50,有五个组,则每组将包含 10 行。

  --演示例题,建一个table

Createtable rankorder(ordered int,qty int)
go

  --插入数据

   Insert rankorder values(30,10)
  insert rankordervalues(10,10)
  insert rankordervalues(80,10)
  insert rankordervalues(40,10)
  insert rankordervalues(30,15)
  insert rankordervalues(30,20)
  insert rankordervalues(22,20)
  insert rankordervalues(21,20)
  insert rankordervalues(10,30)
  insert rankordervalues(30,30)
  insert rankordervalues(40,40)
  go

  --查询出各类排名

SELECTorderid,qty,
ROW_NUMBER() OVER(ORDER BYqty) AS rownumber,
RANK() OVER(ORDERBY qty)AS [rank],
DENSE_RANK() OVER(ORDERBYqty) AS denserank,
NTILE(3) OVER(ORDERBY qty)AS [NTILE]
FROM rankorder
ORDERBY qty

  --结果

  --ROW_NUMBER()是按qty由小到大逐一排名,不并列,排名连续

  --RANK()是按qty由小到大逐一排名,并列,排名不连续

  --DENSE_RANK()是按qty由小到大逐一排名,并列,排名连续

  --NTILE()是按qty由小到大分成3组逐一排名,并列,排名连续

Orderidqty  rownumberrankdenserankNTILE
  30101111
  10102111
  80103111
  40104111
  30155522
  30206632
  22207632
  21208632
  10309943
  303010943
  4040111153

 SQL 2005实现排名非常方便,但是用SQL 2000实现排名就比较麻烦,下面是SQL 2000的实现代码:

  --RANK在sql 2000中的实现

Selectorderid,qty,
(selectcount(1)+1fromrankorderwhereqty
fromrankorderr
ORDERBYqty
go

  --ROW_NUMBER在sql 2000中的实现

  --利用临时表和IDENTITY(函数)

Selectidentity(int,1,1)as[ROW_NUMBER],orderid,qty
into#tem
fromrankorder
selectorderid,qty,[ROW_NUMBER]
from#tem
droptable#tem
go
--DENSE_RANKsql2000中的实现
select identity(int,1,1)asids,qty
into#t
fromrankorder
groupbyqty
orderbyqty
selectr orderid,r.qty,t.idsas[DENSE_RANK]
from rankorderrjoin#tt
onr.qty=t.qty
droptable#t

  排名函数是与窗口函数OVER()配合一起使用的。

  如果借助OVER子句的参数PARTITION BY,就可以将结果集分为多个分区。排名函数将在每个分区内进行排名.

  --例题

SELECTorderid,qty,
DENSE_RANK() OVER(ORDERBYqty)AS a,
DENSE_RANK() OVER(PARTITIONBY orderid ORDER BY qty) AS b
FROM rankorder
ORDER BY qty

  --说明:

  --a列是在全部记录上进行的排名

  --b列是把orderid中的记录分成了10,21,22,30,40,80这6个区,再在每个区上进行的排名。

orderidqtyab
101011
301011
401011
801011
301522
302033
212031
222031
103042
303044
404052

呵呵,太棒了

SELECT 班级名称,姓名,考生成绩,ROW_NUMBER() OVER(PARTITION BY 班级名称ORDER BY 考生成绩desc) AS 成绩排序号自然连号,RANK() OVER (PARTITION BY 班级名称ORDER BY 考生成绩desc) AS 成绩排序号不连号, DENSE_RANK() OVER(PARTITION BY 班级名称ORDER BY 考生成绩) AS 成绩排序号重复连号 FROM student   ORDER BY 班级名称,考生成绩desc ;

  我们看到排名函数可以很简便的得到各种类型的排名

  以下是我对4个排名函数的类比表格:

  排名连续性 排名并列性

  RANK() 不一定连续 有并列

  DENSE_RANK() 连续 有并列

  ROW_NUMBER() 连续 无并列

  NTILE() 连续 有并列

SQL Server 排名函数,布布扣,bubuko.com

SQL Server 排名函数

上一篇:RHEL6.4 apache搭建http网站,安装日志分析工具awstats


下一篇:jQuery编程的最佳实践