oracle
中聚合函数
rank
和
dense_rank
取科目前三名
.txt13
母爱是迷惘时苦口婆心的规劝;
母爱是远行时一声殷切的叮咛;母爱是孤苦无助时慈祥的微笑。
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)