2005每日一学]排名函数（3）

[SQLServer_2005]

一：

排名函数为分区中的每一行返回一个排名值。根据所用的函数，某些行可能与其他行接收到相同的值。排名函数具有不确定性。

Transact-SQL 提供下列4个排名函数：

RANK NTILE

DENSE_RANK ROW_NUMBER

二：

RANK函数：

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

Transact-SQL 语法约定

语法

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


参数
< partition_by_clause >
将 FROM 子句生成的结果集划分成 RANK 函数适用的分区。

< order_by_clause >
确定将 RANK 值应用于分区中的行时所基于的顺序。

如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。例如，如果两位顶尖销售员具有相同的 SalesYTD 值，则他们将并列第一。由于已有两行排名在前，所以具有下一个最大 SalesYTD 的销售人员将排名第三。因此，RANK 函数并不总返回连续整数。

返回类型
bigint

示例：
CREATE TABLE T1
(
ID INT IDENTITY(1,1),
A INT
)
GO
INSERT INTO T1 SELECT 23
UNION ALL SELECT 34
UNION ALL SELECT 56
UNION ALL SELECT 56
UNION ALL SELECT 34
UNION ALL SELECT 56
UNION ALL SELECT 56
GO
SELECT ID,A, RANK() OVER(ORDER BY A DESC) AS RANK
FROM T1
ORDER BY RANK,ID
GO
DROP TABLE T1
GO

结果：

3 56 1
4 56 1
6 56 1
7 56 1
2 34 5
5 34 5
1 23 7


DENSE_RANK ( )函数：

大体与RANK（）函数一样，区别在于
返回结果集分区中行的排名，在排名中没有任何间断。

把上面的例子查询改为：
SELECT ID,A, DENSE_RANK() OVER(ORDER BY A DESC) AS RANK
FROM T1
ORDER BY RANK,ID
GO

结果为：

3 56 1
4 56 1
6 56 1
7 56 1
2 34 2
5 34 2
1 23 3

可以看出区别来了吧！

[SQLServer_2005]

ROW_NUMBER()函数:

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

参数
<partition_by_clause>

将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。

<order_by_clause>

确定将 ROW_NUMBER 值分配给分区中的行的顺序。

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

返回类型
bigint

示例：


CREATE TABLE STU
(
STU_NAME VARCHAR(10),
STU_SCORE FLOAT
)
GO
INSERT STU SELECT 'A',95
UNION ALL SELECT 'B',67
UNION ALL SELECT 'D',79
UNION ALL SELECT 'E',88
UNION ALL SELECT 'C',72
GO
SELECT STU_NAME,STU_SCORE,ROW_NUMBER() OVER(ORDER BY STU_SCORE) AS ROW_NUMBER
FROM STU
ORDER BY ROW_NUMBER
GO

结果为：

B 67 1
C 72 2
D 79 3
E 88 4
A 95 5

然后这时候我们再插入一行纪录：
INSERT INTO STU SELECT 'F',67
GO

查询：
SELECT STU_NAME,STU_SCORE,ROW_NUMBER() OVER(PARTITION BY STU_SCORE ORDER BY STU_SCORE) AS ROW_NUMBER
FROM STU
ORDER BY STU_SCORE,ROW_NUMBER
GO

结果变为：
B 67 1
F 67 2
C 72 1
D 79 1
E 88 1
A 95 1



NTILE()函数：

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

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


参数
integer_expression
一个正整数常量表达式，用于指定每个分区必须被划分成的组数。integer_expression 的类型可以为 int 或 bigint。

注意：
integer_expression 只能引用 PARTITION BY 子句中的列。integer_expression 不能引用在当前 FROM 子句中列出的列。



<partition_by_clause>
将 FROM 子句生成的结果集划分成 RANK 函数适用的分区。

< order_by_clause >
确定 NTILE 值分配到分区中各行的顺序。

备注
如果分区的行数不能被 integer_expression 整除，则将导致一个成员有两种大小不同的组。
按照 OVER 子句指定的顺序，较大的组排在较小的组前面。
例如，如果总行数是 53，组数是 5，则前三个组每组包含 11 行，其余两个组每组包含 10 行。
另一方面，如果总行数可被组数整除，则行数将在组之间平均分布。
例如，如果总行数为 50，有五个组，则每组将包含 10 行。

返回类型

bigint


示例：

根据T1的例子修改查询为：

SELECT ID,A,NTILE(3) OVER(ORDER BY A) AS NTILE
FROM T1
GO

结果为：
1 23 1
2 34 1
5 34 1
6 56 2
7 56 2
3 56 3
4 56 3

更多详细资料参考“Books Online"

以往帖子：
[2005每日一学]DDL触发器（1）
http://community.csdn.net/Expert/topic/4910/4910909.xml?temp=.5234644
2005每日一学]XML数据类型（2）
http://community.csdn.net/Expert/topic/4913/4913373.xml?temp=.579159

[点点星灯]

http://www.microsoft.com/china/msdn/library/data/sqlserver/05TSQLEnhance.mspx?mfr=true

[liangpei2008]

强烈支持LZ，受益非浅啊！