SQL 求中位数

原始表table_name 如下：

1.使用row_number函数正反排序。

注意：使用row_number排序时，相同数值的排序可能会乱序，所以在order by子句中加入主键排序。

select *,row_number() over(order by amount,id) ak,
row_number() over(order by amount desc,id desc) dk from table_name;

结果数据如下：

2.筛选中位数位置的数据

并对筛选的中位数位置的数据求均值，即为中位数。

select avg(amount) as medain from (select * from (select *,row_number() over(order by amount,id desc) ak,
row_number() over(order by amount desc,id) dk from table_name
) t
where cast(ak as signed) - cast(dk as signed) = 0
or abs(cast(ak as signed) - cast(dk as signed)) = 1) s;

结果数据如下：