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

select job, round((case when avg1 mod 1 <> 0 then avg1 - 0.5 else avg1 end), 0) as start, round((case when avg1 mod 1 <> 0 then avg1 + 0.5 else avg1 end), 0) as end
from
(select job, avg(rank1) as avg1
from 
(select job, row_number()over(partition by job order by score) as rank1
from grade) as t1
group by job) as t2
order by job