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

select t.id, t.job,t.score from (
                            select *,avg(score) over (partition by job) avg_  from grade 
) as t
where t.score > t.avg_
order by t.id

三种方式：子查询，内连接，窗口函数

法一：窗口函数

如上【大概意思就是从窗口函数里取】

法二：内连接

select t1.* from grade t1

left join (select job,avg(score) avg_from grade group by job) t2

on t1.job=t2.job

where t1.score>t2.avg_

order by t1.id

法三：子查询

select * from grade t1

where score>(select avg(score) from grade t2

where t1.job=t2.job)#一定不要忘where t1.job=t2.job，否则报错

order by id asc