oracle取销售额前三的函数,oracle中聚合函数rank和dense_rank取科目前三名

教师节双语祝福语

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)