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

select *
from (
    select *,
    dense_rank() over(partition by job order by score desc) as t_rank
    from grade
     ) as t3
where (job, t3.t_rank) in (
                          select g.job,
                            round((case when substring_index(middle, '.', -1)=0
                                   then middle
                                   when substring_index(middle, '.', -1)>0
                                   then middle-0.5 end), 0) as start
                            from grade as g
                            left join (
                                # 2,t2,根据人数奇偶数计算中位数
                                select *,
                                (case
                                 when substring_index(j_cnt / 2, '.', -1) = 0
                                 then ((j_cnt / 2) + ((j_cnt/2)+1))/2
                                 when substring_index(j_cnt / 2, '.', -1) > 0
                                 then (j_cnt + 1) / 2
                                 end) as middle
                                from (
                                    # 1,t1,查询每个岗位分别有多少人
                                    select job, count(distinct id) as j_cnt
                                    from grade
                                    group by job
                                     ) as t1) as t2 using(job)
                            group by job
                            order by job
                          )
or (job, t3.t_rank) in (
                       select g.job,
                        round((case when substring_index(middle, '.', -1)=0
                               then middle
                               when substring_index(middle, '.', -1)>0
                               then middle+0.5 end), 0)  as end
                        from grade as g
                        left join (
                            # 2,t2,根据人数奇偶数计算中位数
                            select *,
                            (case
                             when substring_index(j_cnt / 2, '.', -1) = 0
                             then ((j_cnt / 2) + ((j_cnt/2)+1))/2
                             when substring_index(j_cnt / 2, '.', -1) > 0
                             then (j_cnt + 1) / 2
                             end) as middle
                            from (
                                # 1,t1,查询每个岗位分别有多少人
                                select job, count(distinct id) as j_cnt
                                from grade
                                group by job
                                 ) as t1) as t2 using(job)
                        group by job
                        order by job
                       )
order by id;