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

select
id
,table1.job
,score
,rank1 t_rank
from
(
select
id
,job
,score
,row_number()over(partition by job order by score desc) rank1
from grade
)table1
left join
(
SELECT 
job
,floor(( count(*) + 1 )/ 2 ) AS "start"
,floor(( count(*) + 2 )/ 2 ) AS 'end' 
FROM grade 
GROUP BY job 
)table2
on table1.job = table2.job
where rank1 = start or rank1 = end
order by 1