创建学生表,(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;