转：http://www.cnblogs.com/kkun/archive/2011/08/12/2136034.html
RANK函数为SQL2K5版本中新增,功能为:返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一
先来造基础数据,与之前ROW_NUMBER函数的数据表一样,但数据不一样,完整脚本如下
--DROP TABLE T
CREATE TABLE T(GRP_A VARCHAR(20),GRP_B VARCHAR(20),GRP_C VARCHAR(20),VAL INT)
INSERT INTO T(GRP_A,GRP_B,GRP_C,VAL)
SELECT 'a1','b1','c1',10 union all
SELECT 'a1','b1','c2',10 union all
SELECT 'a1','b2','c2',40 union all
SELECT 'a1','b2','c3',40 union all
SELECT 'a1','b2','c3',50 union all
SELECT 'a2','b3','c3',12 union all
SELECT 'a2','b3','c3',22 union all
SELECT 'a2','b3','c3',32
SELECT * FROM T
先看下最基础的查询:
SELECT * FROM T
返回结果集如下:
GRP_A GRP_B GRP_C VAL
-------------------- -------------------- -------------------- -----------
a1 b1 c1 10
a1 b1 c2 10
a1 b2 c2 40
a1 b2 c3 40
a1 b2 c3 50
a2 b3 c3 12
a2 b3 c3 22
a2 b3 c3 32
(8 行受影响)
现在加上RANK()函数,并且按GRP_A字段分区,按VAL字段正序排序,SQL脚本如下:
SELECT
*,
RANK()OVER(PARTITION BY GRP_A ORDER BY VAL ASC) AS [ORDER]
FROM T
结果集如下
GRP_A GRP_B GRP_C VAL ORDER
-------------------- -------------------- -------------------- ----------- --------------------
a1 b1 c1 10 1
a1 b1 c2 10 1
a1 b2 c2 40 3
a1 b2 c3 40 3
a1 b2 c3 50 5
a2 b3 c3 12 1
a2 b3 c3 22 2
a2 b3 c3 32 3
(8 行受影响)
可以看到额外增加了一列: ORDER列
简单分析下,SQL先按照GRP_A字段分成了两个区:a1和a2,分别为前五行和后三行
在a1分区内,VAL值分别为10,10,40,40,50,假设这是某班某组某科童鞋们的成绩的话,老师需要一个排名,谁是第一,谁是第二,那个ORDER列就是了!
前两名同学并列第一,中间两名同学并列第三,然后有一个第五名,这就是RANK函数的功能,其中第二名第四名缺失!
与RANK函数类似的还有一个函数叫DENSE_RANK()方法,与前者不同的是该函数要求排名连续,前边的条件都不用变,仅把RANK改为DENSE_RANK即可,SQL脚本:
SELECT
*,
DENSE_RANK()OVER(PARTITION BY GRP_A ORDER BY VAL ASC) AS [ORDER]
FROM T
结果集:
GRP_A GRP_B GRP_C VAL ORDER
-------------------- -------------------- -------------------- ----------- --------------------
a1 b1 c1 10 1
a1 b1 c2 10 1
a1 b2 c2 40 2
a1 b2 c3 40 2
a1 b2 c3 50 3
a2 b3 c3 12 1
a2 b3 c3 22 2
a2 b3 c3 32 3
(8 行受影响)
新的排名出来了,两名并列第1,两名并列第2,然后一名第3,DENSE_RANK函数
事实上,无论RANK还是ROW_NUMBER函数,其中PARTITION关键字至关重要,其前可以使用大部分聚合函数,如AVG,SUM,COUNT以及本篇中的RANK,DENSE_RANK和前篇中的ROW_NUMBER,分区是关键,其它看情况发挥~
