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

方法1 窗口函数

SELECT id, job, score
FROM grade a
WHERE score> (
	SELECT AVG(score)
	FROM grade b
	WHERE a.job = b.job)
ORDER BY a.id

select id, job, score 
from (
    select * , avg(score) over(partition by job) av 
    from grade
) a
where score > av 
order by id 

方法2 join

select id, job, score 
from grade a 
left join (
    select b.job, avg(score) av 
    from grade b  
    group by job 
) c using (job)
where a.score > c.av
order by a.id;