一次MySQL join执行错误分析

最新推荐文章于 2026-08-04 16:04:55 发布

原创最新推荐文章于 2026-08-04 16:04:55 发布 · 800 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

数据库

本文详细分析了一次MySQL JOIN操作出现的错误，主要涉及LEFT JOIN时数据异常的问题。通过逐步排查，发现错误源于在ON条件中使用了NULL值进行比较，导致不应出现的0值。解决方案是调整ON条件，确保在JOIN时正确处理NULL值，同时注意聚合函数对NULL的处理。总结了在LEFT JOIN和RIGHT JOIN中避免此类问题的经验。

一次MySQL join执行错误分析

分析

练习一道MySQL题目按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

分析：

要获得学生的学号，姓名，因此需要student表
要获得所有课程的成绩，需要score 和course表
使用join获取所有课程的成绩，使用group by获取平均成绩
使用order by desc排序

sql:

SELECT
	st.s_id "学号",
	st.s_name "姓名",
	avg( sc.s_score ) "平均分",
	sc1.s_score "语文",
	sc2.s_score "数学",
	sc3.s_score"英语" 
FROM
	student st
	LEFT JOIN score sc1 ON st.s_id = sc1.s_id 
	AND sc1.c_id = '01'
	LEFT JOIN score sc2 ON sc1.s_id = sc2.s_id 
	AND sc2.c_id = '02'
	LEFT JOIN score sc3 ON sc2.s_id = sc3.s_id 
	AND sc3.c_id = '03'
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	st.s_id 
ORDER BY
	avg( sc.s_score ) DESC;

得到如下结果
sql1

初次观察发现以平均分排序没有问题，但是小数点没有统一，也有很多null，因此进行优化，将sql语句改为如下：

SELECT
	st.s_id "学号",
	st.s_name "姓名",
	round(ifnull(avg( sc.s_score ),0),2) "平均分",
	ifnull(sc1.s_score,0) "语文",
	ifnull(sc2.s_score,0) "数学",
	ifnull(sc3.s_score,0) "英语" 
FROM
	student st
	LEFT JOIN score sc1 ON st.s_id = sc1.s_id 
	AND sc1.c_id = '01'
	LEFT JOIN score sc2 ON sc1.s_id = sc2.s_id 
	AND sc2.c_id = '02'
	LEFT JOIN score sc3 ON sc2.s_id = sc3.s_id 
	AND sc3.c_id = '03'
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	st.s_id 
ORDER BY
	avg( sc.s_score ) DESC;

得到数据如下
sql2

观察数据发现，排序没有问题，小数点保留尾数也没问题，也没有null的问题，但是07号学生平均分为93.5，但是三门分数全是0，明显的错误。

初步怀疑是left join的错误，于是写了一个left join

SELECT
	* 
FROM
	student
	LEFT JOIN score ON student.s_id = score.s_id 
	AND score.c_id = '01';

结果

sql3

发现score表中有为null的情况，这是因为使用left join 在o左边的表的字段全部展示，右边的表，如果不满足条件会显示为null。

在第一次的sql中的on条件后使用了，sc1.s_id=sc2.s_id等该字段进行判断，null与null是没法使用=进行判断，这就导致了不满足条件，返回了null，该字段为null则返回0，这就导致了第一次sql中存在不应该为0时也返回0的情况，而avg函数的参数使用的是sc.s_score因此不影响。

再次修改sql

SELECT
	st.s_id "学号",
	st.s_name "姓名",
	round(ifnull(avg( sc.s_score ),0),2) "平均分",
	ifnull(sc1.s_score,0) "语文",
	ifnull(sc2.s_score,0) "数学",
	ifnull(sc3.s_score,0) "英语" 
FROM
	student st
	LEFT JOIN score sc1 ON st.s_id = sc1.s_id 
	AND sc1.c_id = '01'
	LEFT JOIN score sc2 ON st.s_id = sc2.s_id 
	AND sc2.c_id = '02'
	LEFT JOIN score sc3 ON st.s_id = sc3.s_id 
	AND sc3.c_id = '03'
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	st.s_id 
ORDER BY
	avg( sc.s_score ) DESC;

得到结果为
sql4

依旧有一点小问题，三门科目必修，07号学生的平均分有问题，这是因为avg函数忽略了为null的行，因此调整sql

SELECT
	st.s_id "学号",
	st.s_name "姓名",
	round(ifnull(sum( sc.s_score )/3,0),2) "平均分",
	ifnull(sc1.s_score,0) "语文",
	ifnull(sc2.s_score,0) "数学",
	ifnull(sc3.s_score,0) "英语" 
FROM
	student st
	LEFT JOIN score sc1 ON st.s_id = sc1.s_id 
	AND sc1.c_id = '01'
	LEFT JOIN score sc2 ON st.s_id = sc2.s_id 
	AND sc2.c_id = '02'
	LEFT JOIN score sc3 ON st.s_id = sc3.s_id 
	AND sc3.c_id = '03'
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	st.s_id 
ORDER BY
	sum( sc.s_score )/3 DESC;