本文通过例子演示了Oracle分析函数 —— rank, dense_rank, row_number的用法。
//首先建score表
create table score(
course nvarchar2(20),
stu_name nvarchar2(20),
score number(2));
//插入数据
insert into score values ('Math','Tough1',95);
insert into score values ('Math','Tough2',93);
insert into score values ('Math','Tough3',94);
insert into score values ('Math','Tough4',95);
insert into score values ('Math','Tough5',94);
//查看
select * from score order by score desc;
| COURSE | STU_NAME | SCORE |
| Math | Tough1 | 95 |
| Math | Tough4 | 95 |
| Math | Tough5 | 94 |
| Math | Tough3 | 94 |
| Math | Tough2 | 93 |
- rank() over
按course分组，并按score排名。
若score相同，rank会跳跃式排名——例如有两个第一名，接下来直接是第三名，没有第二名。
select course, stu_name, score,
rank() over(partition by course order by score desc) "RANK"
from score;
| COURSE | STU_NAME | SCORE | RANK |
| Math | Tough1 | 95 | 1 |
| Math | Tough4 | 95 | 1 |
| Math | Tough5 | 94 | 3 |
| Math | Tough3 | 94 | 3 |
| Math | Tough2 | 93 | 5 |
- dense_rank() over
按course分组，并按score排名。
若score相同，dense_rank不会跳跃式排名——例如有两个第一名，接下来是第二名。
select course, stu_name, score,
dense_rank() over(partition by course order by score desc) "RANK"
from score;
| COURSE | STU_NAME | SCORE | RANK |
| Math | Tough1 | 95 | 1 |
| Math | Tough4 | 95 | 1 |
| Math | Tough5 | 94 | 2 |
| Math | Tough3 | 94 | 2 |
| Math | Tough2 | 93 | 3 |
- row_number() over
按course分组，并按score排名。
若score相同，row_number排名也不一样。
select course, stu_name, score,
row_number() over(partition by course order by score desc) "RANK"
from score;
| COURSE | STU_NAME | SCORE | RANK |
| Math | Tough1 | 95 | 1 |
| Math | Tough4 | 95 | 2 |
| Math | Tough5 | 94 | 3 |
| Math | Tough3 | 94 | 4 |
| Math | Tough2 | 93 | 5 |