SQL Server 排名函数

以下是对这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_RANK在sql2000中的实现

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

呵呵。太棒了

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() 连续 有并列