Simoneoracle rank函数怎么用
的有关信息介绍如下：
rank函数是oracle分析函数中的一个，
主要用法是：
rank() over (order by 排序字段 顺序)
rank() over (partition by 分组字段 order by 排序字段 顺序)
本例将结合具体示例来说明如何使用rank函数
首先创建示例表，再初始化几条测试数据
create table t_score
(
id number primary key,
student_id number(3),
student_name char(8) not null,
sub_name varchar2(20),
score number(10,2)
);
insert into t_score (id, student_id, student_name, sub_name, score)
values (1, 1, 'John', 'c#', 90);
insert into t_score (id, student_id, student_name, sub_name, score)
values (2, 1, 'John', 'php', 97);
insert into t_score (id, student_id, student_name, sub_name, score)
values (3, 1, 'John', 'java', 85);
insert into t_score (id, student_id, student_name, sub_name, score)
values (4, 2, 'Lili', 'c#', 75);
insert into t_score (id, student_id, student_name, sub_name, score)
values (5, 2, 'Lili', 'php', 55);
insert into t_score (id, student_id, student_name, sub_name, score)
values (6, 2, 'Lili', 'java', 91);
insert into t_score (id, student_id, student_name, sub_name, score)
values (7, 3, 'Tom', 'c#', 40);
insert into t_score (id, student_id, student_name, sub_name, score)
values (8, 3, 'Tom', 'php', 89);
insert into t_score (id, student_id, student_name, sub_name, score)
values (9, 3, 'Tom', 'java', 98);
commit;
查询java科目的名次
select sc.student_id,sc.student_name,sub_name,sc.score,
rank() over (order by score desc) 名次
from t_score sc
where sub_name='java';
对比：rank()与dense_rank()：非连续排名与连续排名(都是简单排名)
select sc.student_id,sc.student_name,sub_name,sc.score,
dense_rank() over (order by score desc) 名次
from t_score sc
where sub_name='java';
查询各学生各科排名(分区排名)
select sc.student_id,sc.student_name,sub_name,sc.score,
rank() over (partition by sub_name order by score desc) 名次
from t_score sc;
查询各同学总分
select student_id,student_name,sum(score) sum_score from t_score
group by student_id,student_name;
根据总分查询各同学名次
select x.*,
rank() over (order by sum_score desc) 名次
from (
select student_id,student_name,sum(score) sum_score from t_score
group by student_id,student_name ) x
以上就是针对rank函数的使用案例说明
