MySQL 转置结果集以利于跨行计算

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

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

一.需求

对来自多个行的数据进行计算。
为便于计算，可以把这些行转置到列中，这样所有需要的值都包含于1行中。

deptno 20 是总工资最高的部门，执行下列查询可以确认这一列:

mysql> select deptno, sum(sal) as sal
-> from emp
-> group by deptno;
±-------±---------+
| deptno | sal |
±-------±---------+
| 10 | 8750.00 |
| 20 | 10875.00 |
| 30 | 9400.00 |
±-------±---------+
3 rows in set (0.00 sec)

现在要计算deptno 20 和deptno 10之间以及deptno 20 和deptno 30 之间的总工资之差。

二.解决方案

使用聚集函数sum及case表达式转换总和，然后，在select列表中编写表达式:

select  d20_sal - d10_sal as d20_10_diff,
        d20_sal - d30_sal as d20_30_diff
  from  (
select  sum(case when deptno = 10 then sal end) as d10_sal,
        sum(case when deptno = 20 then sal end) as d20_sal,
        sum(case when deptno = 30 then sal end) as d30_sal
  from  emp
        ) totals_by_dept;

测试记录:

mysql> select  d20_sal - d10_sal as d20_10_diff,
    ->         d20_sal - d30_sal as d20_30_diff
    ->   from  (
    -> select  sum(case when deptno = 10 then sal end) as d10_sal,
    ->         sum(case when deptno = 20 then sal end) as d20_sal,
    ->         sum(case when deptno = 30 then sal end) as d30_sal
    ->   from  emp
    ->         ) totals_by_dept;
+-------------+-------------+
| d20_10_diff | d20_30_diff |
+-------------+-------------+
|     2125.00 |     1475.00 |
+-------------+-------------+
1 row in set (0.00 sec)