1.rank()分析函数
语法:
RANK() OVER([PARTITION BY expr1[,expr2,..,exprn]] ORDER BY expr1[,expr2,..,exprn])
功能: 先将记录按PARTITION分组,组内再以ORDER BY排序,算出当前记录在组内所处的级别(名次). PRATITION条件省略时,表明不分组,或将全录记录作为一组
例子: 有表如下:
SQL> select * from test; NAME SUBJECT SCORE 已选择9行。 |
执行语句如下:
SQL> break on name skip 1; NAME SUBJECT SCORE SCORE_RANK bb english 96 1 cc art 78 1 已选择9行。 |
SQL> select * from (select name ,subject,score ,rank() over ( partition by subject order by score desc) score_rank from test) where score_rank<=3; NAME SUBJECT SCORE SCORE_RANK aa chinese 90 1 bb english 96 1 已选择9行。 |
2.dense_rank函数
DENSE_RANK分析函数
语法:DENSE_RANK() OVER([PARTITION BY expr1[,expr2,..,exprn]] ORDER BY expr1[,expr2,..,exprn])
已创建 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 by subject order by score desc) score_rank from test) where score_rank<=3;
已选择9行。 |
SQL> select * from (select name ,subject,score ,dense_rank() over ( partition by name order by score desc) score_rank from test) where score_rank<=3; NAME SUBJECT SCORE SCORE_RANK 已选择12行。 |
SQL> select * from (select name ,subject,score,rank() over ( partition by name order by score desc) NAME SUBJECT SCORE SCORE_RANK 已选择12行。 SQL> select * from (select name ,subject,score,rank() over ( partition by subject order by score desc) score_rank from test) where score_rank<=3; NAME SUBJECT SCORE SCORE_RANK 已选择9行。 |