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

来源：互联网发布：苹果6网络不可用编辑：程序博客网时间：2024/06/05 00:21

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;

0 0