最近正准备面试,所以本来不怎么熟悉的SQL语句迫切需要练习,学习一下

在此感谢 笨鸟先飞-天道酬勤 大佬的博客:https://blog.csdn.net/dehu_zhou/article/details/52881587

在题17:按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT a.*
,SUM(CASE WHEN b.Cid='01' THEN b.score ELSE 0 END) AS s01
,SUM(CASE WHEN b.Cid='02' THEN b.score ELSE 0 END) AS s02
,SUM(CASE WHEN b.Cid='03' THEN b.score ELSE 0 END) AS s03
,AVG(ifnull(b.score,0)) as avs
FROM Student a
LEFT JOIN SC b
ON a.Sid=b.Sid
GROUP BY 1,2,3,4
ORDER BY avs DESC ;

最终的结果如下:

MySQL--关于MySQL练习过程中遇到的AVG()函数处理空值的问题-LMLPHP

在这里感觉到了疑惑,为啥07的平均成绩没有计算s01这个值呢,导致最终结果与我想的不符(按3取平均值)

百思不得其解

后来感谢群内大佬的解答

原因在于:

  AVG()函数统计的是表中的数据,对于SC表而言,需要计算的score值只有2个,所以即便在后续的join语句中,产生了null值,并置为0.在这里也是按SC表中的2个数值计算,而非3个数值;

解决办法:

  方法1:再套一层查询,将结果作为临时表,对临时表字段进行统计

select t.*, round((s01+s02+s03)/3,2) as avs from (
SELECT a.*
,SUM(CASE WHEN b.Cid='01' THEN b.score ELSE 0 END) AS s01
,SUM(CASE WHEN b.Cid='02' THEN b.score ELSE 0 END) AS s02
,SUM(CASE WHEN b.Cid='03' THEN b.score ELSE 0 END) AS s03
FROM Student a
LEFT JOIN SC b
ON a.Sid=b.Sid
GROUP BY 1,2,3,4
) t ORDER BY avs desc;

  返回结果:

MySQL--关于MySQL练习过程中遇到的AVG()函数处理空值的问题-LMLPHP

  方法2:根据课程表,补全课程信息

select * from Student t
LEFT JOIN (
select a.Sid
,max(case when a.Cid='01' THEN a.score ELSE 0 END) as s01
,max(case when a.Cid='02' THEN a.score ELSE 0 END) as s02
,max(case when a.Cid='03' THEN a.score ELSE 0 END) as s03
,round(avg(case when a.score is null then 0 else a.score end),2) as avs
from SC a group BY a.Sid ) t1
on t.Sid = t1.Sid
;

  返回结果:

MySQL--关于MySQL练习过程中遇到的AVG()函数处理空值的问题-LMLPHP

05-26 07:28