创建学生表，（sno,name,score,class),求每个班级中低于最高成绩的那些人

创建学生表，（sno,name,score,class),求每个班级中低于最高成绩的那些人

create table student(
 sno number(10),
 name varchar2(12),
 score number(3),
 class varchar2(20)
);

insert into student values(2012105,'lisi',89,'class1');

insert into student values(2012103,'lidsi',100,'class1');

insert into student values(2012104,'lisgd',89,'class1');

insert into student values(2012101,'lidfssi',98,'class2');

insert into student values(2012100,'lisdi',66,'class2');

insert into student values(2012106,'ldgisi',87,'class2');

insert into student values(2012107,'lgisi',89,'class3');

insert into student values(2012108,'lisgi',78,'class3');

insert into student values(2012109,'ldfisi',74,'class3');

select * from student;

select s.name,s.sno,s.class,s.score,b.maxscore from student s,(select class,max(score) maxscore from student group by class)b where s.class=b.class and s.score < b.maxscore order by s.class;