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

套中套之连环套娃

select c.job,
case when d=1 then round(c.a/2,0)
else round(c.a/2) end,
case when d=1 then round(c.a/2,0)
else round(c.a/2)+1 end
from
(select *,case when
b.a%2=1 then 1
else 2 end d
from
(select *,max(r) a
from
(select *,
ROW_NUMBER()over(partition by job order by score desc) r
from grade) a
group by job) b) c

虽然很麻烦，但我成功了....