题解 | #考试分数(二)#
考试分数(二)
http://www.nowcoder.com/practice/f456dedf88a64f169aadd648491a27c1
法1：来自课本
SELECT * FROM grade x
WHERE score>(SELECT AVG(score) FROM grade y WHERE x.job=y.job)
ORDER BY id;
法2：原表和平均成绩表做连接
SELECT id,grade.job,grade.score FROM grade
JOIN (SELECT job,AVG(score) score FROM grade GROUP BY job) a
ON grade.job=a.job
WHERE grade.score>a.score
ORDER BY id;
