MySQL 转置带小计的结果集

Love The Way You Lie 2022-11-01 10:55 140阅读 0赞

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

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

# 一.需求 #

要创建一个包含小计的报表，然后对接过做转置变换，使报表更易读。

例如，要求创建一个报表，它显示每个部门、部门经理，以及这些经理手下员工的总工资。  
另外，还要返回两个小计:每个部门中各经理手下员工工资的总和、结果集中所有工资总和(部门小计的总和)。

目前有如下报表:  
±-------±-----±---------+  
| deptno | mgr | sal |  
±-------±-----±---------+  
| 10 | 7782 | 1300.00 |  
| 10 | 7839 | 2450.00 |  
| 10 | NULL | 3750.00 |  
| 20 | 7566 | 6000.00 |  
| 20 | 7788 | 1100.00 |  
| 20 | 7839 | 2975.00 |  
| 20 | 7902 | 800.00 |  
| 20 | NULL | 10875.00 |  
| 30 | 7698 | 6550.00 |  
| 30 | 7839 | 2850.00 |  
| 30 | NULL | 9400.00 |  
| NULL | NULL | 24025.00 |  
±-------±-----±---------+

要提供一个更易读的报表，而且希望把上面的结果集转换为下列格式，它会使报表的意义更为清晰:

# 二.解决方案 #

首先，使用group by的rollup拓展生产小计，然后进行经典的转置变化(聚集及CASE表达式)，以创建报表所需要的列。

使用GROUPING函数，很容易确定哪些值是小计(即，正常情况下没有，加了rollup才有的行)。

+------+---------+----------+---------+-------+
    | mgr  | dept10  | dept20   | dept30  | total |
    +------+---------+----------+---------+-------+
    | 7902 |    0.00 |   800.00 |    0.00 |  NULL |
    | 7839 | 2450.00 |  2975.00 | 2850.00 |  NULL |
    | 7788 |    0.00 |  1100.00 |    0.00 |  NULL |
    | 7782 | 1300.00 |     0.00 |    0.00 |  NULL |
    | 7698 |    0.00 |     0.00 | 6550.00 |  NULL |
    | 7566 |    0.00 |  6000.00 |    0.00 |  NULL |
    | NULL | 3750.00 | 10875.00 | 9400.00 |  NULL |
    +------+---------+----------+---------+-------+

测试记录:

mysql> select  mgr,
        ->         sum(case deptno when 10 then sal else 0 end)  dept10,
        ->         sum(case deptno when 20 then sal else 0 end)  dept20,
        ->         sum(case deptno when 30 then sal else 0 end)  dept30,
        ->         sum(case flag when '11' then sal else null end) total
        ->   from  (
        -> select  deptno,mgr,sum(sal) sal,
        ->         cast(grouping(deptno) as char(1))||
        ->         cast(grouping(mgr) as char(1)) flag
        ->   from  emp
        ->  where  mgr is not null
        ->  group  by  deptno,mgr with rollup
        ->         ) x
        -> group by mgr
        -> order by mgr desc
        -> ;
    +------+---------+----------+---------+-------+
    | mgr  | dept10  | dept20   | dept30  | total |
    +------+---------+----------+---------+-------+
    | 7902 |    0.00 |   800.00 |    0.00 |  NULL |
    | 7839 | 2450.00 |  2975.00 | 2850.00 |  NULL |
    | 7788 |    0.00 |  1100.00 |    0.00 |  NULL |
    | 7782 | 1300.00 |     0.00 |    0.00 |  NULL |
    | 7698 |    0.00 |     0.00 | 6550.00 |  NULL |
    | 7566 |    0.00 |  6000.00 |    0.00 |  NULL |
    | NULL | 3750.00 | 10875.00 | 9400.00 |  NULL |
    +------+---------+----------+---------+-------+
    7 rows in set, 1 warning (0.00 sec)

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