oracle中聚合函数rank及dense_rank取科目前三名.doc

oracle中聚合函数rank及dense_rank取科目前三名 oracle中聚合函数rank和dense_rank取科目前三名.txt爱空空情空空，自己流浪在街中；人空空钱空空，单身苦命在打工；事空空业空空，想来想去就发疯；碗空空盆空空，生活所迫不轻松。总之，四大皆空！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 数学