MySQL案例：多表连接查询表格数据

多表连接查询

 

解题思路： 

1.select course.CourseId,course.CourseName,teacher.* from course,teacher where course.TeaId=teacher.TeaId;
将课程表和教师表的信息合并，然后查询每个课程对应的老师

2.select course.CourseName,max(score.Score) from score,course where course.CourseId=score.CourseId group by score.CourseId;
将分数表和课程表合并，按照课程分组，再查询每科最大分数

3.select course.CourseName,t2.Score as '最高分',student.* from student,course,(select score.StuId,score.Score,score.CourseId from score,(select CourseId,max(Score) as m from score group by CourseId)t1 where score.Score=t1.m and score.CourseId = t1.CourseId)t2 where student.StuId= t2.StuId and t2.CourseId=course.CourseId order by t2.CourseId asc;
第一步是要先只知道每科最高分的分数值
第二步从分数表和分数值表中获取最高分学生的id、课程id、最高分值
第三步从上一个结果中和学生表、课程表中获取对应的学生信息，课程名

4.条件：总分最高
查询内容：学生信息
因为总分最高是多少不知道，所以先要查询,命名为t1：select sum(Score) as s1 from score group by StuId order by su desc limit 1;
统计总分按照学生分组t2：select StuId,sum(Score) as s2 from score group by StuId;
查询学生信息，从上面两张表和studen表：select student.*,t2.s2 from student,(select sum(Score) as s1 from score group by StuId order by s1 desc limit 1)t1,(select StuId,sum(Score) as s2 from score group by StuId)t2 where t1.s1 = t2.s2 and student.StuId=t2.StuId;

5.条件：花无缺，最高分
内容：性别
teacher-course-score-student
第一步先找花无缺带的班级：select TeaId from teacher where TeaName='花无缺';
第二步根据teaid从course表中找courseid：select course.CourseId from course,(select TeaId from teacher where TeaName='花无缺')t1 where course.TeaId = t1.TeaId;
第三步根据课程id去分数表里面找最高分的值：select t2.CourseId,max(score.Score) as m1 from score,(select course.CourseId from course,(select TeaId from teacher where TeaName='花无缺')t1 where course.TeaId = t1.TeaId)t2 where score.CourseId=t2.CourseId group by score.CourseId;
第四步才是统计各科最高分,找出花无缺那科id的最高分的所有人：select score.StuId,score.CourseId,score.Score from score,(select t2.CourseId,max(score.Score) as m1 from score,(select course.CourseId from course,(select TeaId from teacher where TeaName='花无缺')t1 where course.TeaId = t1.TeaId)t2 where score.CourseId=t2.CourseId group by score.CourseId)t3 where score.CourseId=t3.CourseId and score.Score = t3.m1 group by score.CourseId;
第五步根据上一个结果中的学生id去student表里面找学生性别：select student.StuSex from student,(select score.StuId,score.CourseId,score.Score from score,(select t2.CourseId,max(score.Score) as m1 from score,(select course.CourseId from course,(select TeaId from teacher where TeaName='花无缺')t1 where course.TeaId = t1.TeaId)t2 where score.CourseId=t2.CourseId group by score.CourseId)t3 where score.CourseId=t3.CourseId and score.Score = t3.m1 group by score.CourseId)t4 where t4.StuId=student.StuId

6.select student.StuName from student,(select score.StuId,score.CourseId,score.Score from score,(select t2.CourseId,min(score.Score) as m1 from score,(select course.CourseId from course,(select TeaId from teacher where TeaName='姚明')t1 where course.TeaId = t1.TeaId)t2 where score.CourseId=t2.CourseId group by score.CourseId)t3 where score.CourseId=t3.CourseId and score.Score = t3.m1 group by score.CourseId)t4 where t4.StuId=student.StuId

7.条件：每科最高分，人数>=2
内容：科目
第一步查每科最高分：select CourseId,max(Score) as m1 from score group by CourseId;
第二步从分数表中过滤科目和上一个一样，分数也一样的学生的信息：select score.*,t1.m1 from score,(select CourseId,max(Score) as m1 from score group by CourseId)t1 where score.CourseId=t1.CourseId and score.Score=t1.m1;
第三步统计大于2的courseid：select t2.CourseId,count(*) from (select score.*,t1.m1 from score,(select CourseId,max(Score) as m1 from score group by CourseId)t1 where score.CourseId=t1.CourseId and score.Score=t1.m1)t2 group by t2.CourseId having count(*) >=2;
第四步，那上面的结果去course表中找课程名字：select course.* from course,(select t2.CourseId,count(*) from (select score.*,t1.m1 from score,(select CourseId,max(Score) as m1 from score group by CourseId)t1 where score.CourseId=t1.CourseId and score.Score=t1.m1)t2 group by t2.CourseId having count(*) >=2)t3 where course.CourseId= t3.CourseId;

8.先查学生最多的三门课id：select CourseId,count(*) from score group by CourseId order by count(*) desc limit 3;
在根据id去课程表里找课程名字：select course.CourseId,course.CourseName from course,(select CourseId,count(*) from score group by CourseId order by count(*) desc limit 3)t1 where course.CourseId = t1.CourseId;

找均分最高的三门课id： select CourseId,avg(Score) from score group by CourseId order by avg(Score) desc limit 3;

再根据id去课程表中找名字：select course.CourseId,course.CourseName from course,(select CourseId,avg(Score) from score group by CourseId order by avg(Score) desc limit 3)t2 where course.CourseId = t2.CourseId;

最后一步，将两个表进行连接：(select course.CourseId as '前三个是人数最多的，后三个是均分最高的',course.CourseName from course,(select CourseId,count(*) from score group by CourseId order by count(*) desc limit 3)t1 where course.CourseId = t1.CourseId) union all (select course.CourseId,course.CourseName from course,(select CourseId,avg(Score) from score group by CourseId order by avg(Score) desc limit 3)t2 where course.CourseId = t2.CourseId)

9.先统计男生的总分：select * from student where StuSex = '男';
再拿id去统计总分：select t1.StuSex,sum(score.Score) from score,(select * from student where StuSex = '男')t1 where score.StuId=t1.StuId 

再统计女生的：select t2.StuSex,sum(score.Score) from score,(select * from student where StuSex = '女')t2 where score.StuId=t2.StuId 

最后联合两张表：(select t1.StuSex,sum(score.Score) from score,(select * from student where StuSex = '男')t1 where score.StuId=t1.StuId) union all (select t2.StuSex,sum(score.Score) from score,(select * from student where StuSex = '女')t2 where score.StuId=t2.StuId);

10.条件:英语、现行代数
内容：英语均分、线代总分
如果条件是已知的可以直接用，如果是未知的，则需要通过查询获取
第一步：select CourseId,CourseName from course where CourseName = '英语' or CourseName = '线性代数';
第二步，过滤出分数表里只属于英语和线代的数据：select score.CourseId,score.Score,t1.CourseName from score,(select CourseId,CourseName from course where CourseName = '英语' or CourseName = '线性代数')t1 where score.CourseId = t1.CourseId;
第三步，从上面的表中查英语均分：select avg(t2.Score) from (select score.CourseId,score.Score,t1.CourseName from score,(select CourseId,CourseName from course where CourseName = '英语' or CourseName = '线性代数')t1 where score.CourseId = t1.CourseId)t2 where t2.CourseName='英语';
第四步，从上上一个表中查线代总分:select sum(t3.Score) from (select score.CourseId,score.Score,t1.CourseName from score,(select CourseId,CourseName from course where CourseName = '英语' or CourseName = '线性代数')t1 where score.CourseId = t1.CourseId)t3 where t3.CourseName='线性代数';
最后一步合并：(select t2.CourseName,avg(t2.Score) as '结果' from (select score.CourseId,score.Score,t1.CourseName from score,(select CourseId,CourseName from course where CourseName = '英语' or CourseName = '线性代数')t1 where score.CourseId = t1.CourseId)t2 where t2.CourseName='英语') union all (select t3.CourseName,sum(t3.Score) from (select score.CourseId,score.Score,t1.CourseName from score,(select CourseId,CourseName from course where CourseName = '英语' or CourseName = '线性代数')t1 where score.CourseId = t1.CourseId)t3 where t3.CourseName='线性代数');

11.条件：单科分数>单科均分
内容：学生信息
第一步先查单科均分：select CourseId,avg(Score) as a1 from score group by CourseId;
第二步找单科分数>单科均分的: select score.*,t1.a1 from score,(select CourseId,avg(Score) as a1 from score group by CourseId)t1 where score.CourseId=t1.CourseId and score.Score > t1.a1;
第三步拿上面的学生id去学生表里找信息：select student.*,t2.CourseId,t2.Score,t2.a1 from student,(select score.*,t1.a1 from score,(select CourseId,avg(Score) as a1 from score group by CourseId)t1 where score.CourseId=t1.CourseId and score.Score > t1.a1)t2 where student.StuId = t2.StuId;

上面是不去重的写法，现在展示去重的写法：select distinct(t3.StuId) from (select student.* from student,(select score.*,t1.a1 from score,(select CourseId,avg(Score) as a1 from score group by CourseId)t1 where score.CourseId=t1.CourseId and score.Score > t1.a1)t2 where student.StuId = t2.StuId)t3;

12.select t3.StuSex,count(*) from (select student.*,t2.CourseId,t2.Score,t2.a1 from student,(select score.*,t1.a1 from score,(select CourseId,avg(Score) as a1 from score group by CourseId)t1 where score.CourseId=t1.CourseId and score.Score > t1.a1)t2 where student.StuId = t2.StuId)t3 group by t3.StuSex;

去重的：select student.StuSex,count(*) from student,(select distinct(t3.StuId) from (select student.* from student,(select score.*,t1.a1 from score,(select CourseId,avg(Score) as a1 from score group by CourseId)t1 where score.CourseId=t1.CourseId and score.Score > t1.a1)t2 where student.StuId = t2.StuId)t3)t4 where student.StuId=t4.StuId group by student.StuSex;

13.先找有一个及格的人的id，去重：select distinct(StuId) from score where Score >= 60;
统计有成绩的人的id：select distinct(score.StuId) as d2 from score;

select StuSex,count(*) from student, (select distinct(StuId) from score where StuId not in (select distinct(StuId) from score where Score>=60))t1 where student.StuId=t1.StuId group by StuSex; 

14.select StuSex,count(*) from student,(select distinct(StuId) from score where StuId not in (select distinct(StuId) from score where Score <60))t1 where student.StuId=t1.StuId and student.StuAge >=18 group by StuSex;
先找只要有一科不及格的人的id，然后从有成绩的人里面过滤掉这些id，剩下的就是每科都及格的人的id，去重，然后拿这个id去学生表里按照性别分组，统计年龄大于等于18的人数即可。