题解 | #考试分数(二)#

-- subquery
select g.id,g.job,g.score
from grade g join(
    select job,avg(score) avg_score
    from grade group by 1
) avg_scores on g.job = avg_scores.job
where g.score > avg_scores.avg_score
order by 1;


-- command table expression
with avg_scores as(
    select job,avg(score) avg_score
    from grade
    group by 1
)
select id,g.job,score
from grade g,avg_scores a
where g.job = a.job and g.score > a.avg_score
order by 1;