题解 | 考试分数(二)

SELECT g.id, g.job, g.score
FROM(
    SELECT *, AVG(score) OVER(PARTITION BY job ) avg
FROM grade
) g 
WHERE g.score > g.avg
ORDER BY g.id;

SELECT g.id, g.job, g.score
FROM grade g
WHERE g.score > (
    SELECT AVG(score)
    FROM grade gd
    WHERE g.job = gd.job
    GROUP BY job
)
ORDER BY g.id;


SELECT g.id, g.job, g.score
FROM grade g
JOIN(
    SELECT job, AVG(score) avg_sco
    FROM grade
    GROUP BY job 
) t 
    ON g.job = t.job
WHERE g.score > t.avg_sco
ORDER BY g.id