Rank与Dense_Rank都可以返回排序后的排名,不同点是Rank的排名是非连续的,而Dense_Rank的排名是连续的。
1.执行下列script创建测试表
Create table test (a number, b number);
insert into test values (1,1);
insert into test values (1,1);
insert into test values (1,2);
insert into test values (1,2);
insert into test values (1,3);
insert into test values (2,1);
insert into test values (2,2);
insert into test values (2,2);
insert into test values (2,3);
commit;
2.执行下列select语句查看区别
select rank() over(partition by a order by b) "Rank" ,
dense_rank() over(partition by a order by b) "Dense_Rank"
from test
order by a;
N M Rank Dense_Rank
---------- ---------- ---------- ----------
1 1 1 1
1 1 1 1
1 2 3 2
1 2 3 2
1 3 5 3
2 1 1 1
2 2 2 2
2 2 2 2
2 3 4 3
首先按a进行分组,然后根据b的排序结果进行排名,从结果就可以发现两个函数的不同。
1.执行下列script创建测试表
Create table test (a number, b number);
insert into test values (1,1);
insert into test values (1,1);
insert into test values (1,2);
insert into test values (1,2);
insert into test values (1,3);
insert into test values (2,1);
insert into test values (2,2);
insert into test values (2,2);
insert into test values (2,3);
commit;
2.执行下列select语句查看区别
select rank() over(partition by a order by b) "Rank" ,
dense_rank() over(partition by a order by b) "Dense_Rank"
from test
order by a;
N M Rank Dense_Rank
---------- ---------- ---------- ----------
1 1 1 1
1 1 1 1
1 2 3 2
1 2 3 2
1 3 5 3
2 1 1 1
2 2 2 2
2 2 2 2
2 3 4 3
首先按a进行分组,然后根据b的排序结果进行排名,从结果就可以发现两个函数的不同。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13129975/viewspace-624208/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13129975/viewspace-624208/