Sql Server2005 4个排名函数: RANK(),DENSE_RANK(),ROW_NUMBER(),NTILE()

Sql Server2005 4个排名函数: RANK(),DENSE_RANK(),ROW_NUMBER(),NTILE()
--演示例题，建一个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() 连续 有并列