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

背景知识 中位数的计算方法： https://baike.baidu.com/item/%E4%B8%AD%E4%BD%8D%E6%95%B0/3087401?fr=aladdin

# # 1,t1,查询每个岗位分别有多少人
# select job, count(distinct id) as j_cnt
# from grade
# group by job

# # 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

# 3,t3,查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序
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,
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;