oracle中聚合函数rank和dense_rank取科目前三名

Rank的基本语法为：

 

RANK ( ) OVER ( [query_partition_clause] order_by_clause )

 

1 建表语句

 

create table test_qjk_score(
stu int primary key,
subject varchar2(30),
mark int
);

 

 

insert into test_qjk_score(stu,subject,mark)values(1,'语文',85);
insert into test_qjk_score(stu,subject,mark)values(2,'语文',15);
insert into test_qjk_score(stu,subject,mark)values(3,'语文',25);
insert into test_qjk_score(stu,subject,mark)values(4,'语文',35);
insert into test_qjk_score(stu,subject,mark)values(5,'语文',45);
insert into test_qjk_score(stu,subject,mark)values(6,'语文',55);
insert into test_qjk_score(stu,subject,mark)values(7,'语文',65);
insert into test_qjk_score(stu,subject,mark)values(8,'语文',75);

insert into test_qjk_score(stu,subject,mark)values(9,'数学',83);
insert into test_qjk_score(stu,subject,mark)values(10,'数学',13);
insert into test_qjk_score(stu,subject,mark)values(11,'数学',23);
insert into test_qjk_score(stu,subject,mark)values(12,'数学',33);
insert into test_qjk_score(stu,subject,mark)values(13,'数学',43);
insert into test_qjk_score(stu,subject,mark)values(14,'数学',53);
insert into test_qjk_score(stu,subject,mark)values(15,'数学',63);
insert into test_qjk_score(stu,subject,mark)values(16,'数学',73);


insert into test_qjk_score(stu,subject,mark)values(17,'英语',87);
insert into test_qjk_score(stu,subject,mark)values(18,'英语',17);
insert into test_qjk_score(stu,subject,mark)values(19,'英语',27);
insert into test_qjk_score(stu,subject,mark)values(20,'英语',37);
insert into test_qjk_score(stu,subject,mark)values(21,'英语',47);
insert into test_qjk_score(stu,subject,mark)values(22,'英语',57);
insert into test_qjk_score(stu,subject,mark)values(23,'英语',67);
insert into test_qjk_score(stu,subject,mark)values(24,'英语',77);

 

 

2.执行

select * from (select rank() over(partition by subject order by mark desc) rk,test_qjk_score.* from test_qjk_score) T  where T.rk<=3;

 

3.就可以得到结果了,结果如下:

RK    STU           SUBJECT    MARK
1        9             数学    83
2       16            数学    73
3       15            数学    63
1       17           英语    87
2       24           英语    77
3      23            英语    67
1      1             语文    85
2      8             语文    75
3      7             语文    65

 

dense_rank与rank()用法相当，但是有一个区别：dence_rank在并列关系是，相关等级不会跳过。rank则跳过

　　

　　例如：表

　　

　　A　　　　　　B　　　　　　C

　　a　　　　　liu　　　　　wang

　　a　　　　　jin　　　　　shu

　　a　　　　　cai　　　　　kai

　　b　　　　　yang　　　　 du

　　b　　　　　lin　　　　　ying

　　b　　　　　yao　　　　　cai

　　b　　　　　yang　　　　 99

　　

　　例如：当rank时为：

　　

　　select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test3 m

　　

　　 A　　　　　B　　　　　　 C　　　　　LIU

　　 a　　　　　cai　　　　　 kai　　　　　1

　　 a　　　　　jin　　　　　 shu　　　　　2

　　 a　　　　　liu　　　　　 wang　　　　 3

　　 b　　　　　lin　　　　　 ying　　　　 1

　　 b　　　　　yang　　　　　du　　　　　 2

　　 b　　　　　yang　　　　　99　　　　　 2

　　 b　　　　　yao　　　　　 cai　　　　　4

　　

　　而如果用dense_rank时为：

　　

　　select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test3 m

　　

　　 A　　　　　B　　　　　　 C　　　　　LIU

　　 a　　　　　cai　　　　　kai　　　　　   1

　　 a　　　　　jin　　　　　shu　　　　　   2

　　 a　　　　　liu　　　　　wang　　　　       3

　　 b　　　　　lin　　　　　ying　　　　         1

　　 b　　　　　yang　　　　 du　　　　　      2

　　 b　　　　　yang　　　　 99　　　　　      2

　　 b　　　　　yao　　　　　cai　　　　　   3