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

最新推荐文章于 2023-06-13 16:20:11 发布

skylar_

最新推荐文章于 2023-06-13 16:20:11 发布

阅读量1.5k

点赞数

分类专栏： oracle 文章标签： oracle

本文链接：https://blog.csdn.net/u011329762/article/details/37566013

版权

oracle 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

创建学生表，（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;