题解 | 不低于平均分的最低分

别人写的都挺好,我只是分享一下我的思路以及犯的错误: 1.要审题,题目要求是"SQL"比较，那么就需要两张表进行联表； from exam_record a left join examination_info b on a.exam_id = b.exam_id where b.tag = 'SQL' 2.比较的是平均成绩，那么需要先查询"SQL"的平均分 select sum(a.score)/count(a.score) from exam_record a left join examination_info b on a.exam_id = b.exam_id where b.tag = 'SQL'; (备注:个人觉得在这里直接用avg聚合不是很好,勿喷~) 3.题目要求是不低于平均分的最低分,那么思路就是先求大于等于平均分的记录,然后再求最低的,为了方便,我们通过where...and...进行操作

select min(x.score) as min_score_over_avg from exam_record x left join examination_info y on x.exam_id = y.exam_id where y.tag = 'SQL' and x.score >= (select sum(a.score)/count(a.score) from exam_record a left join examination_info b on a.exam_id = b.exam_id where b.tag = 'SQL');