查询每个班的最高分

1.创建测试表score

create table score(
class_no varchar2(10),      --班级
student_name varchar2(20),  --姓名
score number                --分数
);

2.初始化数据

insert into score(class_no,student_name,score) values('n001','park',99);
insert into score(class_no,student_name,score) values('n001','ning',99);
insert into score(class_no,student_name,score) values('n001','tom',79);
insert into score(class_no,student_name,score) values('n001','cat',87);
insert into score(class_no,student_name,score) values('n001','sandy',95);
insert into score(class_no,student_name,score) values('n002','cake',85);
insert into score(class_no,student_name,score) values('n002','mavom',69);
insert into score(class_no,student_name,score) values('n002','tony',90);
insert into score(class_no,student_name,score) values('n002','lisa',99);
insert into score(class_no,student_name,score) values('n002','linda',67);
insert into score(class_no,student_name,score) values('n003','versy',84);
insert into score(class_no,student_name,score) values('n003','peter',97);
insert into score(class_no,student_name,score) values('n003','train',83);
insert into score(class_no,student_name,score) values('n003','rain',80);

3.查询各班最高分（五种方案）

--1.
select *
  from score t
 where not exists (select *
          from score
         where class_no = t.class_no
           and score > t.score);

--2.         
select class_no, student_name, score
  from (select class_no,
               student_name,
               score,
               rank() over(partition by class_no order by score desc) mark
          from score) t
 where mark = 1;

--3. 
select *
  from score t
 where (class_no, score) in
       (select class_no, max(score) score from score t group by t.class_no);

--4.
select s.class_no, student_name, score
  from score s,
       (select class_no, max(score) maxsal from score group by class_no) t
 where s.class_no = t.class_no
   and s.score = t.maxsal;

--5.
select class_no, student_name, score
  from (select class_no,
               student_name,
               score,
               last_value(score) over(partition by class_no order by score rows between unbounded preceding and unbounded following) maxsal
          from score)
 where score = maxsal;

4.执行结果