数据库题目:张三的 MySQL 学习之路(二)
题目难度:困难
题目描述:张三曾经有一个梦想,想要听遍学校里的每一位教师的课程,于是写了一个视图 v_mydream 展示了所有的课程与授课教师的信息,并按教师姓名升序去实现,但是张三发现课程实在是太多太多,梦想需要改一改,于是他把梦想改成了去听每一位教师最受欢迎的课程(听课学生越多越受欢迎,如果该教师没有教授课程则课程与学生数显示为 NULL),学校也非常支持他,听说他最近在学 MySQL,就给了他 2 张表的数据让他自己去找,teachers 表中记录着教师的信息,courses 表中记录着课程信息。张三一看,“ 哇~ 这也太简单了,感谢学校大大提供的数据 ”,可是他正要把原来的梦想视图改写,就发现李四从旁边过。李四肚子饿的呱呱叫,看到张三就问要不要一起去吃饭,张三灵光一闪,“ 这么巧~ 我也打算去吃饭,但是现在我这里有一个很简单的问题,你帮我解决了我就请你吃饭 ”,李四一听还有这等好事,立马答应下来了,结果发现不会。为了不掉面子并且能吃上一顿,就偷偷联系了你,请求你帮他解决。
表定义 1: teachers (教师表)
表定义 2: courses (课程表)
结果视图定义: v_mydream(视图)
样例
输入数据:
courses 表:
teachers 表:
返回结果:
解题思路
1、根据题目要求先修改视图v_mydream
2、先根据teacher_id对教师进行分组,通过课程表计算每个教师的学生总数,再次获取到学生总数最大值的教师,若有教师没有教授课程则课程与学生数显示为 NULL
3、再将其课程表和教师表进行左连接,然后按照teacher_id分组,按照teacher_name升序
获取所需要的结果
源代码
alter view v_mydream as
select
t.name as teacher_name,
c.name as course_name,
c.student_count as student_count
from
teachers as t
left join
courses as c
on
t.id = c.teacher_id
where
(t.id,c.student_count) IN (
select
teacher_id,
max(student_count)
from
courses
group by teacher_id
) or isnull(c.student_count)
order by teacher_name
运行结果
笨猫猫写这道题用了大概15分钟的时间,有点小菜,可能还有更好的解法哦。