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

亦凉 2022-10-24 05:48 157阅读 0赞

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

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][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)

[scott_sql]: https://www.jianshu.com/p/532fe68924cb