MySQL多表查询面试题一

其中分析题意，学生表student是与成绩表score关联，课程表course与教师表teacher关联，由此可以先确定关联关系，学生表为s，课程表为c，教师表为t，成绩表为o。s.s_id=o.s_id，c.t_id=t.t_id

(1)查询所有学生的学号、姓名、选课数、总成绩

学生表与成绩表关联，通过成绩来确认选课数

select s.s_id 学号,s.s_name 姓名,count(s_score) 选课数,sum(s_score) 总成绩 from score o inner join student s on o.s_id = s.s_id group by s.s_id; 

(2)查询学过“张三”老师所教的所有课的同学的学号、姓名

运用子查询来查询从学生的学号查到科目中有相同学号后再通过课程编码去找教师编码最后找到张三

select  s.s_id,s.s_name from student where s_id in(select o.s_id from score,course,teacher where o.c_id= c.c_id and c.t_id=t.t_id and t.t_name='张三');

(3) 查询和“02”号的同学学习的课程完全相同的其他同学学号和姓名

运用子查询先在成绩表中找到学号为02的课程编码过滤出每个人的课程，再对02学习的课程编码通过having进一步过滤后与其他人学习的课程编码与02的比较是否相同来筛选出与02所学课程完全相同的学号姓名。

select s_id from score where c_id in(select c_id from score where s_id='02') group by s_id having count(*) = (select count(*) from score where s_id = '02');

(4)按平均成绩从高到低显示所有学生的“数据库”(c_id='04)、“企业管理”（c_id='01')、“英语”(c_id='06) 三门的课程成绩，按如下形式显示:学生ID，数据库，企业管理，英语，有效课程数，有效平均分

题目可得所有学生学习的课程内容有数据库c_id=04，企业管理c_id=01，英语c_id=06共三门，

select s_id as 学号,c_id='04' as 数据库,c_id='01' as 企业管理,c_id='06' as 英语,(select count(distinct o.c_id) from score o where s.s_id = o.s_id)as 有效课程数, (select avg(o.s_score) from score o where s.s_id=o.s_id ) as 有效平均分 order by 平均成绩 desc;

(5) 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数:课程ID和课程名称
select o.c_id as 课程ID，c_name as 课程名称,sum(case when s_score between 85 and 100 then 1 else 0 end) as '[100-85]',sum(case when s_score between 70 and 85 then 1 else 0 end) as'[85-70]',sum(case when s_score between 60 and 70 then 1 else 0 end) as '[70-60]',sum(case when s_score < 60 then 1 else 0 end) as '[<60]' from score,course where o.c_id = c.c_id group by o.c_id;

综上，如若有误欢迎指出