DENSE_RANK分析函数语法:DENSE_RANK() OVER([PARTITION BY expr1[,expr2,..,exprn]] ORDER BY expr1[,expr2,..,exprn]) SQL> insert into test values('dd','chinese',80);已创建 1 行。SQL> insert into test values('dd','art',78);已创建 1 行。SQL> insert into test values('dd','english',96);已创建 1 行。SQL> select * from (select name ,subject,score,dense_rank()over ( partition bysubjectorder by score desc) score_rank from test) where score_rank<=3;NAME SUBJECT SCORE SCORE_RANK ---------- ---------- ---------- ---------- dd art 96 1 bb 92 2 aa 88 3 aa chinese 90 1 bb 80 2 cc 73 3 bb english 96 1 aa 80 2 cc 60 3已选择9行。 SQL> select * from (select name ,subject,score ,dense_rank() over ( partition bynameorder by score desc) score_rank from test) where score_rank<=3;NAME SUBJECT SCORE SCORE_RANK ---------- ---------- ---------- ---------- aa chinese 90 1 aa art 88 2 aa english 80 3 bb 96 1 bb art 92 2 bb chinese 80 3 cc art 78 1 cc chinese 73 2 cc english 60 3 dd art 96 1 dd 80 2 dd 78 3已选择12行。
SQL> select * from (select name ,subject,score,rank()over ( partition bynameorder by score desc) score_rank from test) where score_rank<=3;NAME SUBJECT SCORE SCORE_RANK ---------- ---------- ---------- ---------- aa chinese 90 1 aa art 88 2 aa english 80 3 bb 96 1 bb art 92 2 bb chinese 80 3 cc art 78 1 cc chinese 73 2 cc english 60 3 dd art 96 1 dd 80 2 dd 78 3已选择12行。SQL> select * from (select name ,subject,score,rank()over ( partition bysubjectorder by score desc) score_rank from test) where score_rank<=3;NAME SUBJECT SCORE SCORE_RANK ---------- ---------- ---------- ---------- dd art 96 1 bb 92 2 aa 88 3 aa chinese 90 1 bb 80 2 cc 73 3 bb english 96 1 aa 80 2 cc 60 3已选择9行。
SQL> insert into test values('dd','chinese',80);已创建 1 行。SQL> insert into test values('dd','art',78);已创建 1 行。SQL> insert into test values('dd','english',96);已创建 1 行。SQL> select * from (select name ,subject,score,dense_rank()over ( partition bysubjectorder by score desc) score_rank from test) where score_rank<=3;NAME SUBJECT SCORE SCORE_RANK
---------- ---------- ---------- ----------
dd art 96 1
bb 92 2
aa 88 3
aa chinese 90 1
bb 80 2
cc 73 3
bb english 96 1
aa 80 2
cc 60 3已选择9行。
SQL> select * from (select name ,subject,score ,dense_rank() over ( partition bynameorder by score desc) score_rank from test) where score_rank<=3;NAME SUBJECT SCORE SCORE_RANK
---------- ---------- ---------- ----------
aa chinese 90 1
aa art 88 2
aa english 80 3
bb 96 1
bb art 92 2
bb chinese 80 3
cc art 78 1
cc chinese 73 2
cc english 60 3
dd art 96 1
dd 80 2
dd 78 3已选择12行。
SQL> select * from (select name ,subject,score,rank()over ( partition bynameorder by score desc)
score_rank from test) where score_rank<=3;NAME SUBJECT SCORE SCORE_RANK
---------- ---------- ---------- ----------
aa chinese 90 1
aa art 88 2
aa english 80 3
bb 96 1
bb art 92 2
bb chinese 80 3
cc art 78 1
cc chinese 73 2
cc english 60 3
dd art 96 1
dd 80 2
dd 78 3已选择12行。SQL> select * from (select name ,subject,score,rank()over ( partition bysubjectorder by score desc) score_rank from test) where score_rank<=3;NAME SUBJECT SCORE SCORE_RANK
---------- ---------- ---------- ----------
dd art 96 1
bb 92 2
aa 88 3
aa chinese 90 1
bb 80 2
cc 73 3
bb english 96 1
aa 80 2
cc 60 3已选择9行。