题解 | #考试分数(二)#
考试分数(二)
http://www.nowcoder.com/practice/f456dedf88a64f169aadd648491a27c1
查询的是所在职业分数大于该职业平均分的成绩信息
首先,查询出各个职业及其平均分,作为t2表,
使用grade表通过job连接t2
加上条件,即t1.job = t2.job and t1.score > t2.avg_score
最后对id升序
select t1.id,t1.job,t1.score
from grade t1
join (select job,avg(score) as avg_score
from grade
group by job) t2
on t1.job = t2.job
where t1.score > t2.avg_score
order by t1.id asc
算法太卷啦