练习4：左外连接、子查询、聚合函数

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

关键词分析： 学生编号、学生姓名、选课总数、所有课程总成绩

第一步： 根据关键词锁定要查询的表为学生表student与成绩表sc。

第二步： 不访先查出所有学生的编号以及姓名，如下：

SELECT Sid,Sname FROM student;

第三步： 利用Sid字段进行聚合，查出sc表中每个学生学习的课程总数以及总成绩。

SELECT Sid,COUNT(Cid),SUM(score) FROM sc GROUP BY Sid;

第四步： 将第二、三步的结果进行关联查询，关联字段为Sid。注意题目要求没成绩的显示null，即学生信息无论如何都要显示，只是没有成绩就把成绩相关的部分显示为null，故用student做主表，左外连接sc表。

SELECT 
  a.Sid,
  a.Sname,
  b.cnt_Cid,
  b.sum_score 
FROM
  student a 
LEFT JOIN (SELECT Sid,COUNT(Cid) cnt_Cid,SUM(score) sum_score FROM sc GROUP BY sid) AS b 
ON a.Sid = b.sid ;

由于student中的Sid与Sname字段的值是一一对应的，所以本题也可以写成下面的形式，即拼接后再聚合筛选。但是如果Sid和Sname存在一对多的关系则只能用上面的子查询形式，原因是我们使用了聚合查询，聚合字段为Sid，但我们却在结果字段中除Sid和聚合函数外还想包含Sname字段，当Sid和Sname是一对多时，只会显示第一个Sname。如Sid为1时，可以对应Sname为张三和李四两个值，此时对Sid进行聚合，并还想输出Sname，只会输出排在前面的张三。

SELECT 
  a.Sid,
  a.Sname,
  COUNT(b.Cid) cnt_Cid,
  SUM(b.score) sum_score 
FROM
  student a 
  LEFT JOIN sc b 
    ON a.`SId` = b.`SId` 
GROUP BY a.`SId` ;

当然，如果我们分组的时候就使用的是Sid和Sname两个字段，那即使Sid和Sname是一对多的关系也不会有问题了，如下

SELECT 
  a.Sid,
  a.Sname,
  COUNT(b.Cid) cnt_Cid,
  SUM(b.score) sum_score 
FROM
  student a 
  LEFT JOIN sc b 
    ON a.`SId` = b.`SId` 
GROUP BY a.`SId` ,a.`Sname`;

注意第四步的代码中，group by 后面用的是a.sid，如果写成了b.sid将会出现错误。因为b.sid中只有01-07号学生的编号，因此只会对a,b两表的01-07号学生进行group by操作，从而产生错误，因为a中还有09-13号学生呀。错误代码以及结果如下：

SELECT 
  a.Sid,
  a.Sname,
  COUNT(b.Cid) cnt_Cid,
  SUM(b.score) sum_score 
FROM
  student a 
  LEFT JOIN sc b 
    ON a.`SId` = b.`SId` 
GROUP BY b.`SId` ;