10-55 查询各专业学生的平均成绩

本题目要求编写SQL语句，
统计各专业的学生选课的平均成绩，如果某专业尚未有任何学生选修课程或成绩为空时，平均分计为0。输出结果集按照major表中的mno升序排序。

表结构:

CREATE TABLE `major` (
  `mno` char(2) NOT NULL,
  `mname` varchar(20) NOT NULL,
  PRIMARY KEY (`mno`)
);
CREATE TABLE `stu` (
  `sno` char(4) NOT NULL,  -- 学号
  `sname` char(8) NOT NULL,  -- 姓名
  `sex` tinyint(1) DEFAULT NULL,  -- 性别
  `mno` char(2) DEFAULT NULL,  -- 专业号
  `birdate` datetime DEFAULT NULL,  -- 出生日期
  `memo` text,  --  备注
  PRIMARY KEY (`sno`),
    CONSTRAINT `fk_stu_mno` FOREIGN KEY (`mno`) REFERENCES `major` (`mno`)
);
CREATE TABLE `sc` (
  `sno` char(4) NOT NULL,
  `cno` char(4) NOT NULL,
  `grade` decimal(6,1) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`),
  CONSTRAINT `fk_sc_sno` FOREIGN KEY (`sno`) REFERENCES `stu` (`sno`)
);
​

表样例

请在这里给出上述表结构对应的表样例。例如

major表：

stu表：

sc表：

输出样例:

请在这里给出输出样例。例如：

思路：

    刚开始我在代码里面写了注释，然后修修改改半天一直提交不起，后面把注释删了才能提交，我还以为是我自己的问题。言归正传：

    这道题有两个着重难点，一个是ifnull()。一个是多表查询。

    1.首先讲讲ifnull()这里，上道题我们是把ifnull()嵌套在avg里面的，而这道题为什么要弄在外面？其实这道题我才反应过来，严格来说它需要在聚合函数完成之后再进行判断使用，但由于上个题，它是单个值的计算(仔细看它是根据学号来进行运算)，所以说先使用ifnull，问题不大，但这道题就不一样了，它是根据专业来计算平均分，你如果提前ifnull()会导致错误。这究竟是为什么，我们来说一下聚合函数对null值的处理(敲黑板了！)：

    ​首先来看看SQL文档中的这句话，它表达的意思是，当聚合函数在处理null会对他们进行忽略，不做任何处理，而在本题中ifnull()遇到null值时会将它转换为0,0和null很多同学​都把它们认为是一个东西！其实不然，例如我们有20,30,40,null这四组数据，如果是先avg再ifnull，计算过程就是(20+30+40)/3=30，null被忽略，，如果是先ifnull()然后avg的话，计算过程就是(20+30+40+0)/4=22.5，null被转换为0然后参与运算！！结果​天差地别！​！故而以后一般情况下，我们应该养成良好习惯先使用聚合函数在使用ifnull。

    ​2.多表查询，这道题需要提前先将stu和sc连接起来(这里采用的隐式内连接)，合成一个表s，然后采用左连接(为确保包含major的所有专业)，再将，major表和s表连接起来，连接条件就是他们的mno​。​

select  major.mname as '专业',
ifnull(avg(s.grade),0) as '平均成绩'  
from major 
left join 
(select stu.mno,sc.grade 
    from stu,sc
    where stu.sno=sc.sno) as s 
        on major.mno=s.mno
group by major.mno
order by major.mno asc;

   我是阿杰，一名正在努力学习编程的在校大学生，如果你喜欢我的内容，或者有帮助到你，请点赞鼓励支持一下吧！

   更多技术与交流请关注微信公众号：阿杰技术栈