题解 | #得分不小于平均分的最低分#

先上完整代码

select min(e_r.score) as min_score_over_avg
from exam_record e_r join examination_info e_i
on e_r.exam_id = e_i.exam_id
where e_i.tag = 'SQL'
and score >= (select avg(e1.score)
             from exam_record e1 join examination_info e2
             on e1.exam_id = e2.exam_id
             where tag = 'SQL'
             )

最开始我想的是

select min(e_r.score) as min_score_over_avg
from exam_record e_r join examination_info e_i
on e_r.exam_id = e_i.exam_id
where e_i.tag = 'SQL'
and score >= avg(score)

但是发现好像不能这样用，想了想语句的逻辑顺序也就知道了，

所以我就打算整个视图，也算是实践知识就是

create or replace  view  total
as
select e_r.id,e_r.exam_id,e_i.tag,e_r.score
FROM
    exam_record e_r
        LEFT JOIN
    examination_info e_i
    ON
    e_r.exam_id = e_i.exam_id
where tag = 'SQL';


SELECT
    min(total.score) min_score_over_avg
FROM
    total
where
        total.score >= (
            select avg(score)
            from total
        )

但是能在我的mysql运行不能在判题系统运行。。。没事会就行。