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

//select count(*) as total ,job from grade GROUP BY job 先按照job分组计算有多少个
//当total=5 , 中位数start =(5+1)/2=3,total=6,start=6/2=3;
//当total=5 , 中位数end =(5+1)/2=3,total=6,start=6/2+1=4;
//奇数情况下中位数只有一个total=5, end和start都=3

select job,
round((case when total%2=0 then total/2 else (total +1) /2 end),0) as `start`  ,
round((case when total%2=0 then total/2+1 else (total+1)/2 end),0) as `end` 
from (select count(*) as total ,job from grade GROUP BY job )  a order by job;