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` ;