【Oracle】【MySQL】通过SQL语句实现小计、合计
xixuefeng
MySQL, MySQL-DEV, Oracle, SQL&Function
2017-09-18 10:11:04
2,969 次浏览
Oracle&MySQL'SQL
【Oracle】【MySQL】通过SQL语句实现小计、合计已关闭评论
做统计报表开发时，如果对SQL语句了解的不够全面和彻底的话，那么无论是表结构设计还是表的复用都是有所欠缺的。通过一个例子我们测试一下。
样例数据：
本测试中，样例数据库如下：
1）Oracle，采用SCOTT用户下的emp表
2）MySQL，采用 http://xixuefeng.top/archives/727
测试数据如下（以MySQL为例）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
mysql> select * from emp; +-------+--------+-----------+------+---------------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+---------------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+---------------------+---------+---------+--------+ 14 rows in set (0.05 sec) mysql> |
需求：统计【每个部门】中的【每个岗位】的【工资总和】，并统计出，每个【部门的工资小计】及【公司的工资合计】
以上需求非常常见，那么怎么做呢？
1：如果不知道怎么更优的实现需求，那么以实现结果为目的方式，可以采用如下方法：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
mysql> SELECT deptno, job, SUM(sal) FROM emp GROUP BY deptno, job UNION ALL SELECT deptno, NULL, SUM(sal) FROM emp GROUP BY deptno UNION ALL SELECT NULL, NULL, SUM(sal) FROM emp ORDER BY 1 desc, 2 desc; +--------+-----------+----------+ | deptno | job | SUM(sal) | +--------+-----------+----------+ | 30 | SALESMAN | 5600.00 | | 30 | MANAGER | 2850.00 | | 30 | CLERK | 950.00 | | 30 | NULL | 9400.00 | | 20 | MANAGER | 2975.00 | | 20 | CLERK | 1900.00 | | 20 | ANALYST | 6000.00 | | 20 | NULL | 10875.00 | | 10 | PRESIDENT | 5000.00 | | 10 | MANAGER | 2450.00 | | 10 | CLERK | 1300.00 | | 10 | NULL | 8750.00 | | NULL | NULL | 29025.00 | +--------+-----------+----------+ 13 rows in set (0.05 sec) mysql> |
显然，上述SQL实现了统计需求（这个SQL是标准的SQL，所以Oracle和MySQL都是适用的，需要注意的是：Oracle在排序时，null为最大；MySQL则认为null为最小，所以同样的SQL，如果存在null值，那么排序后是不同的）。
但是，你会发现，同样的表，访问了多次，无论是性能还是书写方式上都是不可取的。
2：Oracle的实现方式
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
SQL> SELECT deptno,job, SUM(sal) sal FROM emp GROUP BY ROLLUP(deptno, job); DEPTNO JOB SAL ------ --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 CLERK 1900 20 ANALYST 6000 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 9400 29025 13 rows selected SQL> ## 整一下容（小计和合计都是null，不完美），如下处理： SQL> SELECT decode(nvl(deptno, '0'), '0', 'all deptno', deptno) deptno, decode(nvl(job, '0'), '0', 'all job', job) job, SUM(sal) sal FROM emp GROUP BY ROLLUP(deptno, job); DEPTNO JOB SAL ---------------------------------------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 all job 8750 20 CLERK 1900 20 ANALYST 6000 20 MANAGER 2975 20 all job 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 all job 9400 all deptno all job 29025 13 rows selected SQL> |
3：MySQL呢？当然直接运行Oracle的SQL是不行的，实际上MySQL也有自己的解决方案，在MySQL的官方文档上搜ROLLUP，即可看到相关的用法，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
## 我们直接上整容后的SQL mysql> SELECT if(isnull(deptno)=1,'All deptno',deptno) deptno, if(isnull(job)=1,'All job',job) job, SUM(sal) as sal FROM emp GROUP BY deptno, job WITH ROLLUP; +------------+-----------+----------+ | deptno | job | sal | +------------+-----------+----------+ | 10 | CLERK | 1300.00 | | 10 | MANAGER | 2450.00 | | 10 | PRESIDENT | 5000.00 | | 10 | All job | 8750.00 | | 20 | ANALYST | 6000.00 | | 20 | CLERK | 1900.00 | | 20 | MANAGER | 2975.00 | | 20 | All job | 10875.00 | | 30 | CLERK | 950.00 | | 30 | MANAGER | 2850.00 | | 30 | SALESMAN | 5600.00 | | 30 | All job | 9400.00 | | All deptno | All job | 29025.00 | +------------+-----------+----------+ 13 rows in set (0.04 sec) mysql> |
4：小结
作者本人对MySQL了解有限，MySQL在小计合计方面，差不多也就这样了，没有其他什么变化（通过后续的工作和学习发现不足，作者再完善）。Oracle在小计合计方面还有很多的变化及其他的增强子句，见：http://xixuefeng.top/archives/735