SQL数据库经典题型（三），难点：查出一班各学科分数最高的学生

先创建三个表格，插入数据

create table a(
       stuid number(2) primary key,
       classid varchar2(10) references b(classid),
       stuname varchar2(10)
);
create table b(
       classid varchar2(10) primary key,
       classname varchar2(20)
);
create table c(
       stuid number(2) references a(stuid),
       course varchar2(20),
       score number(3)
);
insert into b(classid, classname) values('A','一班');
insert into b(classid, classname) values('B','二班');
insert into b(classid, classname) values('C','三班');

insert into a(stuid, classid, stuname) values(1,'A','chen');
insert into a(stuid, classid, stuname) values(2,'A','wang');
insert into a(stuid, classid, stuname) values(3,'A','li');
insert into a(stuid, classid, stuname) values(4,'B','yang');
insert into a(stuid, classid, stuname) values(5,'B','huang');
insert into a(stuid, classid, stuname) values(6,'B','feng');
insert into a(stuid, classid, stuname) values(7,'C','zhao');
insert into a(stuid, classid, stuname) values(8,'C','xu');

insert into c(stuid, course, score) values(1,'数学',80);
insert into c(stuid, course, score) values(1,'英语',85);
insert into c(stuid, course, score) values(1,'语文',90);
insert into c(stuid, course, score) values(2,'语文',95);
insert into c(stuid, course, score) values(2,'数学',100);
insert into c(stuid, course, score) values(3,'语文',59);
insert into c(stuid, course, score) values(3,'数学',58);
insert into c(stuid, course, score) values(3,'英语',95);
insert into c(stuid, course, score) values(2,'英语',90);
insert into c(stuid, course, score) values(6,'英语',78);
insert into c(stuid, course, score) values(4,'语文',82);
insert into c(stuid, course, score) values(4,'英语',89);
insert into c(stuid, course, score) values(7,'数学',97);
insert into c(stuid, course, score) values(5,'数学',93);
insert into c(stuid, course, score) values(6,'语文',98);
insert into c(stuid, course, score) values(6,'数学',100);
insert into c(stuid, course, score) values(4,'数学',88);
insert into c(stuid, course, score) values(7,'语文',80);
insert into c(stuid, course, score) values(5,'语文',98);
insert into c(stuid, course, score) values(7,'英文',88);
insert into c(stuid, course, score) values(5,'英语',97);
insert into c(stuid, course, score) values(8,'英语',90);
insert into c(stuid, course, score) values(8,'语文',70);
insert into c(stuid, course, score) values(8,'数学',80);
commit;

--第一题：查出wang的数学分数
select score from a,c
where a.stuid=c.stuid and stuname='wang' and course='数学';
--第二题：查出一班各学科分数最高的学生
--第一步，先查找一班各学科学生最高分数

select course, max(score) from c
where stuid in (select stuid from a where classid=(select classid from b where classname='一班'))
group by course;

--第二步，查找一班的学生学号
select stuid from c where 
stuid in (select stuid from a where classid=(select classid from b where classname='一班'))
group by stuid;
--第三步，找到c表的一班学生信息
select * from c where 
stuid in
(select stuid from c where 
stuid in (select stuid from a where classid=(select classid from b where classname='一班'))
group by stuid);
--将找到的最高分表与c表的一班学生连接，通过最高分及课程号找到对应学号
select stuid,s1.course,mscore
from 
(select * from c where 
stuid in
(select stuid from c where 
stuid in (select stuid from a where classid=(select classid from b where classname='一班'))
group by stuid)) s1,
(select course, max(score) mscore from c
where stuid in (select stuid from a where classid=(select classid from b where classname='一班'))
group by course) s2
where
s1.course=s2.course and s1.score=s2.mscore;
--将上述找到对应学号的表与学生表连接，找到学生名字
select a.stuid,stuname,course,mscore
from a,
(select stuid,s1.course,mscore
from 
(select * from c where 
stuid in
(select stuid from c where 
stuid in (select stuid from a where classid=(select classid from b where classname='一班'))
group by stuid)) s1,
(select course, max(score) mscore from c
where stuid in (select stuid from a where classid=(select classid from b where classname='一班'))
group by course) s2
where
s1.course=s2.course and s1.score=s2.mscore) s1
where a.stuid=s1.stuid;