with t1 as (select job,(count(score)+1)/2 num from grade group by job) -- 取出每个job组中的中位数，5个中位数是3，4个就是2.5

select id,job,score,t2 from (select id,job,score, row_number()over(partition by job order by score desc)t2, num from grade g join t1 using(job))a --添加开窗排序函数，并和t1连接 where if(num=round(num,0),t2 = num,t2 between round(num,0)-1 and round(num,0)) -- 比较排序是中位数的取值。如果中位数是整数，直接去排序等于中位数的值，如果中位数是小数，取四舍五入后的区间，即中位数是2.5，就取排序是2和3的值 order by id