建表与数据准备

建库和建表的操作请参考“多表复杂查询练习及讲解-上”的内容，在此不再赘述。

use day5;  # 切换day5为当前数据库

show tables;

Tables_in_day5
course
score
student
teacher

练习题

第十三题：
查询有缺考课程的学生学号、姓名和所有缺考课程：

这一题的难点在于怎么找到所有学生的缺考信息呢？数据库中有所有学生的考试成绩，但是缺考的情况没有录入学生id、课程id、得分num为0的记录，那么该怎样找出缺考信息呢？
下面是我的解题步骤：
1.将student表和course表做笛卡尔积，得出所有学号、姓名对应所有课程、课程名的记录：

select sid,sname,cid,cname
    from student,course

2.将第一步的表与score表做左连接，这个连接后的大表中num is NULL的记录就是缺考的学号和课程记录，将这些记录筛选出来就能找到缺考课程的学生学号和课程：

select *
    from (select sid,sname,cid,cname from student,course) a
    left join score b
    on  a.sid=b.student_id and a.cid=b.course_id
    where num is NULL;

3.从上表中挑选sid,sname字段，按sid分组，对课程名做group_concat：

select a.sid 学号,a.sname 姓名,group_concat(cname order by cid) 缺考课程
    from(
        select sid,sname,cid,cname
            from student,course) a
    left join score b
    on  a.sid=b.student_id and a.cid=b.course_id
    where num is NULL
    group by a.sid;

查询结果：

小结：
这一题比较难，考验的是逆向思维能力。score表中很多学生缺考，直接查哪个学生缺考了哪门课程好像没有方法。此时我们可以用学生表和课程表做笛卡尔积，那么我们可以得到所有的学生对应了所有的课程全部记录的大表，再将这张大表与score表左连接，此时筛选出左连接后表中num is NULL的记录，那么我们就找到了所有缺考的信息。
还要注意的细节有2条：一是where和group by可以同时使用，where字句必须放在前面。二是group_concat函数有两个参数，参数一是字段名，参数二是按指定字段排序(默认升序，加desc改为倒序)。

第十四题：
找出考试课程与“张宇”相同的学生学号和姓名：

这一题考核的重点是分组聚合、having筛选、group_concat。
1.找出“张宇”考试的所有课程id(记得要排序)，将其保存到变量cid中：

select @cid:=group_concat(course_id order by course_id)
    from score
    group by student_id
    having student_id=(
        select sid
            from student
            where sname='张宇');

2.把score表按学号分组并做分组过滤(过滤条件是所有课程id(记得要排序)与“张宇”相同：

select student_id
    from score
    group by student_id
    having group_concat(course_id order by course_id)=@cid;

3.将上表查询结果与student表连表，显示学号和姓名，要记得排除“张宇”本身，最终完整代码如下：

select @cid:=group_concat(course_id order by course_id)
    from score
    group by student_id
    having student_id=(
        select sid
            from student
            where sname='张宇');
select sid 学号,sname 姓名
    from student a
    right join(
        select student_id
            from score
            group by student_id
            having group_concat(course_id order by course_id)=@cid)b
    on a.sid=b.student_id
    where sname!='张宇';

查询结果：

第十五题：
按所有课程平均成绩从高到低全部学生的id、名字、课程数、平均分、各科成绩：

1.这题要求是按所有课程平均分从高到低排序，实际上并不能算平均分，因为有些学生有些课程没有成绩，所以应该按score表中的总成绩/课程数排序：

select student_id 学号,sum(num)/(select count(*) 课程数 from course) 平均分,count(num) 课程数
    from score
    group by student_id
    order by 平均分 desc;

2.将上表与student表拼接，注意是student表的数据全保留：

select sid 学号,sname 姓名,课程数,平均分
    from student a
    left join(
        select student_id 学号,sum(num)/(select count(*) 课程数 from course) 平均分,count(num) 课程数
            from score
            group by student_id) b
    on a.sid=b.学号
    order by 平均分 desc;

3.接下来要拼接各科成绩和名字的表：

select c.student_id,group_concat(d.cname,':',num order by d.cid) 各课分数
    from score c
    inner join
        course d
    on c.course_id=d.cid
    group by c.student_id;

4.再将第三步表和第四步表拼接起来，形成最终完整的表。另外这里做一个优化，那个课程数可以提前算出来保存在临时变量中，避免重复运算，可以略微提高查询效率：

select @课程数:=count(*)
    from course;
select e.学号,e.姓名,e.课程数,e.平均分,f.各课分数
    from(
        select sid 学号,sname 姓名,课程数,平均分
            from student a
        left join(
            select student_id 学号,sum(num)/@课程数 平均分,count(num) 课程数
                from score
                group by student_id) b
        on a.sid=b.学号) e
    left join(
        select c.student_id,group_concat(d.cname,'',num order by d.cid) 各课分数
            from score c
        inner join
            course d
        on c.course_id=d.cid
        group by c.student_id) f
    on e.学号=f.student_id
    order by 平均分 desc;

查询结果：

总结

第十五题是所有练习题中最难的一题，在写复杂查询的时候要找到思路，按照查询要求一步步地筛选出需要的数据，再一步步地拼接，最终实现输出正确结果。

得出正确的结果以后，认真检查代码找找有没有逻辑缺陷、有没有可提升查询效率的优化空间和和输出的数据格式是否美观等细节。

就像第十五题中：先将课程数计算出来保存到临时变量中，那么能避免重复的查course表的课程数，这样可以略微提高查表效率；另外对于平均分来说没有必要显示小数点后4位，我们可以用round函数对平均分进行小数点后保留1位；还有group_concat时要考虑到score表中的记录是乱序的，所以为了显示效果，那么group_concat中再加上order by 课程id进行排序，最终输出的内容比较美观。

希望学习SQL的朋友不光要会查表，还要考虑效率和美观。

最后，关于MySQL复杂查询的内容全部结束了，希望能对学SQL的朋友有一些帮助，另外有一个小小的建议，写复杂的SQL查询语句的时不要一条SQL语句写一行，最好是参考我上面的案例中写SQL代码的风格。注意每一行代码后面可以写注释，写注释的示范如下：

select @课程数:=count(*)  # 从course表中统计总共有几门课，将其报存到临时变量“课程数”
    from course;

写注释非常重要！若不写注释等过几个月维护代码时自己看自己写的代码都得花老长时间才懂，更别提其他人看你写的代码了。记住，代码的可读性非常重要！！！