Transact-SQL提供了4个排名函数: RANK(),DENSE_RANK(),ROW_NUMBER(),NTILE()
下面是对这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
- create table rankorder(
- orderid int,
- qty int
- )
- go
- --插入数据
- insert rankorder values(30,10)
- insert rankorder values(10,10)
- insert rankorder values(80,10)
- insert rankorder values(40,10)
- insert rankorder values(30,15)
- insert rankorder values(30,20)
- insert rankorder values(22,20)
- insert rankorder values(21,20)
- insert rankorder values(10,30)
- insert rankorder values(30,30)
- insert rankorder values(40,40)
- go
- --查询出各类排名
- SELECT orderid,qty,
- ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
- RANK() OVER(ORDER BY qty) AS [rank],
- DENSE_RANK() OVER(ORDER BY qty) AS denserank ,
- NTILE(3) OVER(ORDER BY qty) AS [NTILE]
- FROM rankorder
- ORDER BY qty
- --结果
- --ROW_NUMBER()是按qty由小到大逐一排名,不并列,排名连续
- --RANK()是按qty由小到大逐一排名,并列,排名不连续
- --DENSE_RANK()是按qty由小到大逐一排名,并列,排名连续
- --NTILE()是按qty由小到大分成3组逐一排名,并列,排名连续
- orderid qty rownumber rank denserank NTILE
- 30 10 1 1 1 1
- 10 10 2 1 1 1
- 80 10 3 1 1 1
- 40 10 4 1 1 1
- 30 15 5 5 2 2
- 30 20 6 6 3 2
- 22 20 7 6 3 2
- 21 20 8 6 3 2
- 10 30 9 9 4 3
- 30 30 10 9 4 3
- 40 40 11 11 5 3
sql 2005实现排名非常方便,但是用sql 2000实现排名就比较麻烦,下面是sql 2000的实现代码:
- --RANK在sql 2000中的实现
- select orderid,qty,
- (select count(1)+1 from rankorder where qty<r.qty) as [rank]
- from rankorder r
- ORDER BY qty
- go
- --ROW_NUMBER在sql 2000中的实现
- --利用临时表和IDENTITY(函数)
- select identity(int,1,1) as [ROW_NUMBER],orderid,qty
- into #tem
- from rankorder
- select orderid,qty,[ROW_NUMBER]
- from #tem
- drop table #tem
- go
- --DENSE_RANK在sql 2000中的实现
- select identity(int,1,1) as ids, qty
- into #t
- from rankorder
- group by qty
- order by qty
- select r.orderid,r.qty,t.ids as [DENSE_RANK]
- from rankorder r join #t t
- on r.qty=t.qty
- drop table #t
- go
排名函数是与窗口函数OVER()配合一起使用的。
如果借助OVER子句的参数PARTITION BY,就可以将结果集分为多个分区。排名函数将在每个分区内进行排名.
- --例题
- SELECT orderid,qty,
- DENSE_RANK() OVER(ORDER BY qty) AS a ,
- DENSE_RANK() OVER(PARTITION BY orderid ORDER BY qty) AS b
- FROM rankorder
- ORDER BY qty
- --说明:
- --a列是在全部记录上进行的排名
- --b列是把orderid中的记录分成了10,21,22,30,40,80这6个区,再在每个区上进行的排名。
- orderid qty a b
- 10 10 1 1
- 30 10 1 1
- 40 10 1 1
- 80 10 1 1
- 30 15 2 2
- 30 20 3 3
- 21 20 3 1
- 22 20 3 1
- 10 30 4 2
- 30 30 4 4
- 40 40 5 2
我们看到排名函数可以很简便的得到各种类型的排名
以下是我对4个排名函数的类比表格:
排名连续性 | 排名并列性 | |
RANK() | 不一定连续 | 有并列 |
DENSE_RANK() | 连续 | 有并列 |
ROW_NUMBER() | 连续 | 无并列 |
NTILE() | 连续 | 有并列 |