MySQL查询求中位数最简单的写法

select avg(a) from (select a,@a:=@a+1 b from t1,(select @a:=0) t2 order by a) t 
 where b between @a/2 and @a/2+1;

create table t1 (id int primary key, a int);
insert into t1 values (1,10),(2,10),(3,20),(4,21),(5,30),(6,30),(7,30),(8,100);

select avg(distinct a)
from (select t1.a from t1,t1 t2 group by t1.a
      having sum(case when t2.a >= t1.a then 1 else 0 end) >= count(*) / 2.0
         and sum(case when t2.a <= t1.a then 1 else 0 end) >= count(*) / 2.0) tmp;

select sum(score) / count(*) as midean
from (
         select a score,
                row_number() over (order by a desc,id desc) as desc_math,
                row_number() over (order by a asc, id asc)   as asc_math
         from t1
     ) as order_table
where asc_math in (desc_math, desc_math + 1, desc_math - 1);