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

第一版：

select * 
from grade g
where score>
(
    select a_score 
    from 
    (
        select avg(score) a_score,job 
        from grade 
        group by job
    ) t
    where g.job=t.job
)
order by id

第二版：

select * 
from grade g
where score>
(
    select avg(score) 
    from grade t
    where g.job=t.job
    group by job
)
order by id

思路：这个题有很多方法，第二版比第一版简洁，少了一个子查询，此题也可以用窗口函数来做。