oracle前三名,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