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

1、求解平均分

select b.job,avg(b.score) as pp
from grade b
group by b.job

2、查找所有的id,job，以及分数

select a.id ,a.job,a.score
from grade a

3、左连接+筛选（高于平均值）

select a.id ,a.job,a.score
from grade a
left join
(select b.job,avg(b.score) as pp
 from grade b
 group by b.job
) as c
on a.job = c.job
where a.score > c.pp

4、升序

select a.id ,a.job,a.score
from grade a
left join
(select b.job,avg(b.score) as pp
 from grade b
 group by b.job
) as c
on a.job = c.job
where a.score > c.pp
order by a.id