解法一 左连接
1、查询每个工作的平均分
select job,avg(score) as avg from grade group by job2、grade表与1表左连接,筛选score大于平均分avg 的信息
select a.* from grade as a left join (select job,avg(score) as avg from grade group by job) as b on a.job=b.job where score>avg order by a.id;解法二 窗口函数
1、原表增加一列平均分
select *,avg(score) over(partition by job) as avg from grade2、在仪表中查询score大于avg 的 信息
select id,job,score from (select *,avg(score) over(partition by job) as avg from grade)as a where score>avg order by id;