MySQL查中位数数据

MySQL查中位数数据

法一 ：利用having的筛选功能将集合里的元素按照大小分为上半部分和下半部分两个子集，同时让这2个子集共同拥有集合正中间的元素，将上下部分集合平均然后得中值
select ROUND(avg(distinct score),2) from (
select t1.score
      from table_name t1,
           table_name t2
      # 如果需要加其他条件,t1和t2都需要加     
      # 例如 WHERE t1.area = '001' and t2.area = '001'
      group by t1.score
			having sum(case when t2.score >= t1.score then 1 else 0 end)
          >= count(*) / 2.0
         and sum(case when t2.score <= t1.score then 1 else 0 end)
          >= count(*) / 2.0
			) tmp

法二 ： 
SELECT score AS median
FROM (SELECT t1.name, t1.score, COUNT(*) AS rank
      FROM table_name AS t1,
           table_name AS t2
      WHERE t1.score < t2.score
         OR (t1.score = t2.score AND t1.name <= t2.name)
      GROUP BY t1.name, t1.score
      ORDER BY rank) t3
WHERE rank = (SELECT (COUNT(*) + 1) DIV 2 FROM table_name)

法三：只能用于Mysql 8
select round(sum(score) / count(*),2) as midean
from (
         select score,
                row_number() over (order by score desc) as desc_Math,
                row_number() over (order by score asc)   as asc_Math
         from table_name
     ) as order_table
where asc_Math in (desc_Math, desc_Math + 1, desc_Math - 1);