MySQL查中位数数据

中位数数据"/>

MySQL查中位数数据

MySQL查中位数数据

法一 ：利用having的筛选功能将集合里的元素按照大小分为上半部分和下半部分两个子集，同时让这2个子集共同拥有集合正中间的元素，将上下部分集合平均然后得中值
select ROUND(avg(distinct score),2) from (
select t1.scorefrom table_name t1,table_name t2# 如果需要加其他条件,t1和t2都需要加     # 例如 WHERE t1.area = '001' and t2.area = '001'group by t1.scorehaving sum(case when t2.score >= t1.score then 1 else 0 end)>= count(*) / 2.0and 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 rankFROM table_name AS t1,table_name AS t2WHERE t1.score < t2.scoreOR (t1.score = t2.score AND t1.name <= t2.name)GROUP BY t1.name, t1.scoreORDER 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_Mathfrom table_name) as order_table
where asc_Math in (desc_Math, desc_Math + 1, desc_Math - 1);