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

select
        t1.job
        ,t1.rank_z
        ,t1.rank_f
from    
        (
            select
                        job
                        ,count(*) over (partition by job)  as `all`
                        ,row_number () over (partition by job order by score ) as 'rank_z'
                        ,row_number () over (partition by job order by score desc) as 'rank_f'
            from
                         grade 
            
        ) t1
where
        t1.rank_z >= t1.all/2 and t1.rank_f >= t1.all/2 and t1.rank_z <= t1.rank_f
order by t1.job