mysql 计算平均数出错 求解

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT DISTINCT s.`s_id`,a.`s_name`,AVG(s.`s_score`)FROM   Score s 
JOIN Student a ON a.`s_id`=s.`s_id`
JOIN Score s1 ON s1.`s_id`=s.`s_id`
JOIN Score s2 ON s2.`s_id`=s.`s_id`
WHERE s.`c_id`='01'AND s.`s_score`<60 AND s1.`c_id`='02'AND s1.`s_score`<60 OR s2.`c_id`='03' AND s2.`s_score`<60
GROUP BY s_id;

之后我把数据库 改成 30 70 0 结果为33
改成 40 30 30 结果为34

你sql里的那些join都变成笛卡尔积数据翻倍了，所以结果肯定是错误的
如果你数据库支持开窗函数的话，可以像下面这么写,只要查一次原表即可

select s_id,s_name,avg(s_score) avg_score from (
select a.s_id,a.s_name,b.s_score,
count(case when b.s_score<60 then 1 end ) over (partition by b.s_id) ct
from Student a,Score b where a.s_id=b.s_id) as t
where ct>=2
group by s_id,s_name

如果是不支持开窗函数的数据库,就只能用类似楼上的方式了，先把满足条件的人查出来，再关联回原数据

select s1.s_id, st.s_name, avg(s1.score) from score s1
left join student st on s1.s_id = st.id
where exists (
-- 查询出两门及其以上不及格课程的同学的学号
select s_id from score s2
where score < 60 and s1.s_id = s2.s_id
group by s_id
having count(s_id) >= 2
)
group by s1.s_id