题解 | #考试分数(五)#
考试分数(五)
https://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0513
select b.id,b.job,b.score,b.rn
from (
select *,rank() over(partition by job order by score desc) rn
from grade a
)b
left join
(
select job,
count(id)/2 as t1,
(count(id)+1)/2 as t2,
(count(id)+2)/2 as t3
from grade c
group by job
)d
on b.job= d.job
where b.rn in (d.t1,d.t2,d.t3)
order by b.id
