MySQL计算中间值

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

一. 问题

计算一列数字值的中间值(中间值就是一组有序元素中间成员的值)。
例如查找deptno 20中工资的中间数。

如下列工资:
mysql> select sal from emp where deptno = 20 order by sal;
±--------+
| sal |
±--------+
| 800.00 |
| 1100.00 |
| 2975.00 |
| 3000.00 |
| 3000.00 |
±--------+
5 rows in set (0.00 sec)

中间数为2975

二.解决方案

使用自联接查找中间数:

select  avg(sal)
   from (
 select e.sal
   from emp e,emp d
 where e.deptno = d.deptno
   and e.deptno = 20
 group by e.sal
having sum(case when e.sal = d.sal then 1 else 0 end)
                           >= abs(sum(sign(e.sal - d.sal)))
) tmp;

测试记录:

mysql> select  avg(sal)
    ->    from (
    ->  select e.sal
    ->    from emp e,emp d
    ->  where e.deptno = d.deptno
    ->    and e.deptno = 20
    ->  group by e.sal
    -> having sum(case when e.sal = d.sal then 1 else 0 end)
    ->                            >= abs(sum(sign(e.sal - d.sal)))
    -> ) tmp;
+-------------+
| avg(sal)    |
+-------------+
| 2975.000000 |
+-------------+
1 row in set (0.01 sec)