SQL 76) 查询各个岗位分数的中位数位置上的所有grade信息

大牛解法）

• 按id升序排序

select B.* 
from
(SELECT job,FLOOR((COUNT(*)+1)/2) AS `start`,FLOOR((COUNT(*)+1)/2)+if(COUNT(*) % 2=1,0,1) AS `end` 
FROM grade  GROUP BY job) A   -- 求出如上题的中位数区间
JOIN
(select g1.*, (
    select count(distinct g2.score) 
    from grade g2 
    where g2.score>=g1.score and g1.job=g2.job) as t_rank
from grade g1 ) B
on (A.job=B.job  and B.t_rank between A.start and A.end)  -- 成绩位于中位数区间
order by B.id

21/4/2
第二次修改，自己的做法，感觉更好理解

select a.id, b.job, a.score , a.t_rank
from 
(select job, floor((count(score)+1)/2) as 'start', floor((count(score)+2)/2) as 'end'
from grade
group by job)b
join
(select id, job, score, dense_rank()over(partition by job order by score desc) t_rank
from grade)a
on b.job = a.job
where a.t_rank between  b.start and b.end
order by a.id asc ;