题解 | #考试分数(二)#
考试分数(二)
https://www.nowcoder.com/practice/f456dedf88a64f169aadd648491a27c1
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
