题解 | 考试分数(二)
考试分数(二)
https://www.nowcoder.com/practice/f456dedf88a64f169aadd648491a27c1
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
查看7道真题和解析
