查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
分析:这道题明显也只用到sc和student两张表,对这两张表进行联合查询,选课总数和课程成绩总和的操作对象都是sc表,因此我们可以构建表r,里面字段是选课总数和成绩总和还有SId,然后跟student表进行联合查询
构建表r
select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc group by sc.sid;
查询结果:
+------+----------+--------------+ | sid | scoresum | coursenumber | +------+----------+--------------+ | 01 | 269.0 | 3 | | 02 | 210.0 | 3 | | 03 | 240.0 | 3 | | 04 | 100.0 | 3 | | 05 | 163.0 | 2 | | 06 | 65.0 | 2 | | 07 | 187.0 | 2 | +------+----------+--------------+
与student进行联合查询:
select student.sid, student.sname,r.coursenumber,r.scoresum from student, (select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc group by sc.sid)r where student.sid = r.sid;
查询结果:
+------+--------+--------------+----------+ | sid | sname | coursenumber | scoresum | +------+--------+--------------+----------+ | 01 | 赵雷 | 3 | 269.0 | | 02 | 钱电 | 3 | 210.0 | | 03 | 孙风 | 3 | 240.0 | | 04 | 李云 | 3 | 100.0 | | 05 | 周梅 | 2 | 163.0 | | 06 | 吴兰 | 2 | 65.0 | | 07 | 郑竹 | 2 | 187.0 | +------+--------+--------------+----------+
到这里结果其实就出来了,但是,这里有一点需要思考的地方,我们的同学其实有13个,但是这里只出现了7个,说明有一些同学是没选课的,这里的查询显然是忽略了这一点,所以如果严谨一些,我们需要吧其他没选课的同学都考虑进去,我们用join就可以给没选课的同学一个null值.
先查询student表拿出所有的学生的sid和sname
select student.sid,student.sname from student;
查询结果;
+------+--------+ | sid | sname | +------+--------+ | 01 | 赵雷 | | 02 | 钱电 | | 03 | 孙风 | | 04 | 李云 | | 05 | 周梅 | | 06 | 吴兰 | | 07 | 郑竹 | | 09 | 张三 | | 10 | 李四 | | 11 | 李四 | | 12 | 赵六 | | 13 | 孙七 | +------+--------+
然后拿出入第一种方法一样查询出总分和科目数
select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc group by sc.sid;
查询结果:
+------+----------+--------------+ | sid | scoresum | coursenumber | +------+----------+--------------+ | 01 | 269.0 | 3 | | 02 | 210.0 | 3 | | 03 | 240.0 | 3 | | 04 | 100.0 | 3 | | 05 | 163.0 | 2 | | 06 | 65.0 | 2 | | 07 | 187.0 | 2 | +------+----------+--------------+
然后left join这两个查询结果,进行查询:
select s.sid, s.sname,r.coursenumber,r.scoresum from ( (select student.sid,student.sname from student )s left join (select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc group by sc.sid )r on s.sid = r.sid );
最终结果:
+------+--------+--------------+----------+ | sid | sname | coursenumber | scoresum | +------+--------+--------------+----------+ | 01 | 赵雷 | 3 | 269.0 | | 02 | 钱电 | 3 | 210.0 | | 03 | 孙风 | 3 | 240.0 | | 04 | 李云 | 3 | 100.0 | | 05 | 周梅 | 2 | 163.0 | | 06 | 吴兰 | 2 | 65.0 | | 07 | 郑竹 | 2 | 187.0 | | 09 | 张三 | NULL | NULL | | 10 | 李四 | NULL | NULL | | 11 | 李四 | NULL | NULL | | 12 | 赵六 | NULL | NULL | | 13 | 孙七 | NULL | NULL | +------+--------+--------------+----------+
以上
原文：https://www.cnblogs.com/lattesea/p/12163857.html