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

最新推荐文章于 2022-05-31 17:01:40 发布

只是甲

最新推荐文章于 2022-05-31 17:01:40 发布

阅读量683

点赞数 2

分类专栏： MySQL从小工到专家之路 # MySQL CookBook 文章标签： mysql sql 数据库所有小计

本文链接：https://blog.csdn.net/u010520724/article/details/113680352

版权

MySQL8.0 GROUP BY 汇总统计工资 SQL脚本

关键词由CSDN通过智能技术生成

MySQL从小工到专家之路同时被 2 个专栏收录

181 篇文章 24 订阅

订阅专栏

MySQL CookBook

73 篇文章 3 订阅

订阅专栏

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

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