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

有三种解答方式：
1、利用数学技巧，中位数的位置取决于总数的奇偶性，count(*) / 2 要么被整除，要么除被整除外还剩0.5。

select 
  job,
  ceil(count(*) / 2) as start,  # 无论count(*) 的奇偶性，中位数第一位置总是它
  ceil(count(*) / 2 + 0.5) as end # 无论count(*) 的奇偶性，中位数第二位置总是它
from 
  grade
group by job 
order by job

2、使用 case when 语句条件判断

select 
  job,
  floor((count(*) + 1) / 2) as start,
  (case when count(*) % 2 = 0 then ceil((count(*) + 1) / 2) else floor((count(*) + 1) / 2) end) as end # 利用整除判断奇偶
from 
  grade
group by job 
order by job

或者

select 
  job,
  floor((count(*) + 1) / 2) as start,
  (case when count(*) & 1 then floor((count(*) + 1) / 2) else ceil((count(*) + 1) / 2) end) as end # 利用位运算判断奇偶
from 
  grade
group by job 
order by job

3、使用 if 语句条件判断

select 
  job,
  floor((count(*) + 1) / 2) as start,
  if(count(*) % 2 = 0, ceil((count(*) + 1) / 2), floor((count(*) + 1) / 2)) as end
from 
  grade
group by job 
order by job

或者

select 
  job,
  floor((count(*) + 1) / 2) as start,
  if(count(*) & 1, floor((count(*) + 1) / 2), ceil((count(*) + 1) / 2)) as end
from 
  grade
group by job 
order by job