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

最新推荐文章于 2024-01-16 17:41:46 发布

原创最新推荐文章于 2024-01-16 17:41:46 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

oracle 专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何通过SQL查询来找出每个班级中分数低于该班最高分的学生名单及其相关信息。示例使用了Oracle SQL语法进行数据库表的创建、数据插入及复杂的连接查询。

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