oj-sql

题目

SQL125 得分不小于平均分的最低分

思路

1. 先求平均数，再子查询条件筛选；

solution

min avg subquery

with t_avg_score as (
    select avg(score) avg_score
    from exam_record record
    join examination_info info
    using (exam_id)
    where info.tag='SQL'
)
select min(score) min_score_over_avg
from exam_record
join examination_info info
using (exam_id)
where info.tag='SQL' and score >= (select avg_score from t_avg_score)
;

也可以通过limit取结果集头一条：

with t_avg_score as (
    select
        avg(score) avg_score
    from exam_record record
    join examination_info info
    using (exam_id)
    where info.tag='SQL' 
)
select score min_score_over_avg
from exam_record 
join examination_info info
using (exam_id)
where info.tag='SQL' and score >= (select avg_score from t_avg_score)
order by score asc
limit 1
;