MySQL 计算所有表达式组合的小计

我就是我 2022-10-28 01:29 151阅读 0赞

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

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

# 一.需求 #

对JOB/DEPTNO的每种组合，求按deptno和job的总工资。并求表EMP中所有工资的总计。

返回的结果集应如:  
±-------±----------±------------------------±---------+  
| deptno | job | category | sal |  
±-------±----------±------------------------±---------+  
| 20 | CLERK | TOTAL BY　DEPT AND JOB | 1900.00 |  
| 30 | SALESMAN | TOTAL BY　DEPT AND JOB | 5600.00 |  
| 20 | MANAGER | TOTAL BY　DEPT AND JOB | 2975.00 |  
| 30 | MANAGER | TOTAL BY　DEPT AND JOB | 2850.00 |  
| 10 | MANAGER | TOTAL BY　DEPT AND JOB | 2450.00 |  
| 20 | ANALYST | TOTAL BY　DEPT AND JOB | 6000.00 |  
| 10 | PRESIDENT | TOTAL BY　DEPT AND JOB | 5000.00 |  
| 30 | CLERK | TOTAL BY　DEPT AND JOB | 950.00 |  
| 10 | CLERK | TOTAL BY　DEPT AND JOB | 1300.00 |  
| NULL | CLERK | TOTAL BY JOB | 4150.00 |  
| NULL | SALESMAN | TOTAL BY JOB | 5600.00 |  
| NULL | MANAGER | TOTAL BY JOB | 8275.00 |  
| NULL | ANALYST | TOTAL BY JOB | 6000.00 |  
| NULL | PRESIDENT | TOTAL BY JOB | 5000.00 |  
| 10 | NULL | TOTAL BY DEPT | 8750.00 |  
| 20 | NULL | TOTAL BY DEPT | 10875.00 |  
| 30 | NULL | TOTAL BY DEPT | 9400.00 |  
| NULL | NULL | GRAND TOTAL FOR TABLE | 29025.00 |  
±-------±----------±------------------------±---------+

# 二.解决方案 #

最近几年，group by中早呢更加的拓展使则个问题相当容易解决。  
如果使用的平台没有提供这种计算各层小计的拓展，那么必须用自连接或标量子查询计算。

select  deptno, job,
            'TOTAL BY　DEPT AND JOB' as category,
            sum(sal)  as sal
      from  emp
     group  by  deptno, job
    union all
    select  null, job, 'TOTAL BY JOB', sum(sal)
      from  emp
     group  by job
    union all
    select  deptno, null,'TOTAL BY DEPT', sum(sal)
      from  emp
     group  by deptno
    union all
    select  null, null,'GRAND TOTAL FOR TABLE', sum(sal)
      from  emp;

测试记录:

mysql> select  deptno, job,
        ->         'TOTAL BY　DEPT AND JOB' as category,
        ->         sum(sal)  as sal
        ->   from  emp
        ->  group  by  deptno, job
        -> union all
        -> select  null, job, 'TOTAL BY JOB', sum(sal)
        ->   from  emp
        ->  group  by job
        -> union all
        -> select  deptno, null,'TOTAL BY DEPT', sum(sal)
        ->   from  emp
        ->  group  by deptno
        -> union all
        -> select  null, null,'GRAND TOTAL FOR TABLE', sum(sal)
        ->   from  emp;
    +--------+-----------+-------------------------+----------+
    | deptno | job       | category                | sal      |
    +--------+-----------+-------------------------+----------+
    |     20 | CLERK     | TOTAL BY　DEPT AND JOB  |  1900.00 |
    |     30 | SALESMAN  | TOTAL BY　DEPT AND JOB  |  5600.00 |
    |     20 | MANAGER   | TOTAL BY　DEPT AND JOB  |  2975.00 |
    |     30 | MANAGER   | TOTAL BY　DEPT AND JOB  |  2850.00 |
    |     10 | MANAGER   | TOTAL BY　DEPT AND JOB  |  2450.00 |
    |     20 | ANALYST   | TOTAL BY　DEPT AND JOB  |  6000.00 |
    |     10 | PRESIDENT | TOTAL BY　DEPT AND JOB  |  5000.00 |
    |     30 | CLERK     | TOTAL BY　DEPT AND JOB  |   950.00 |
    |     10 | CLERK     | TOTAL BY　DEPT AND JOB  |  1300.00 |
    |   NULL | CLERK     | TOTAL BY JOB            |  4150.00 |
    |   NULL | SALESMAN  | TOTAL BY JOB            |  5600.00 |
    |   NULL | MANAGER   | TOTAL BY JOB            |  8275.00 |
    |   NULL | ANALYST   | TOTAL BY JOB            |  6000.00 |
    |   NULL | PRESIDENT | TOTAL BY JOB            |  5000.00 |
    |     10 | NULL      | TOTAL BY DEPT           |  8750.00 |
    |     20 | NULL      | TOTAL BY DEPT           | 10875.00 |
    |     30 | NULL      | TOTAL BY DEPT           |  9400.00 |
    |   NULL | NULL      | GRAND TOTAL FOR TABLE   | 29025.00 |
    +--------+-----------+-------------------------+----------+
    18 rows in set (0.11 sec)

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