COMPUTE 可实现类似于rollup的分组求和功能:
- SQL> select deptno,ename,sum(sal) from scott.emp group by rollup(deptno,ename);
- DEPTNO ENAME SUM(SAL)
- ---------- ---------- ----------
- 10 KING 5000
- 10 CLARK 2450
- 10 MILLER 1300
- 10 8750
- 20 FORD 3000
- 20 ADAMS 1100
- 20 JONES 2975
- 20 SCOTT 3000
- 20 SMITH 800
- 20 10875
- 30 WARD 1250
- 30 ALLEN 1600
- 30 BLAKE 2850
- 30 JAMES 950
- 30 MARTIN 1250
- 30 TURNER 1500
- 30 9400
- DEPTNO ENAME SUM(SAL)
- ---------- ---------- ----------
- 29025
- SQL> BREAK ON report
- SQL> select deptno,ename,sum(sal) empsal from scott.emp group by deptno,ename;
- DEPTNO ENAME EMPSAL
- ---------- ---------- ----------
- 10 MILLER 1300
- 30 MARTIN 1250
- 10 KING 5000
- 20 FORD 3000
- 20 SMITH 800
- 30 BLAKE 2850
- 30 ALLEN 1600
- 20 JONES 2975
- 10 CLARK 2450
- 30 TURNER 1500
- 30 WARD 1250
- 20 SCOTT 3000
- 30 JAMES 950
- 20 ADAMS 1100
- ----------
- sum 29025
- SQL> break on deptno skip 1 on report
- SQL> select deptno,ename,sum(sal) empsal from scott.emp group by deptno,ename order by 1;
- DEPTNO ENAME EMPSAL
- ---------- ---------- ----------
- 10 CLARK 2450
- KING 5000
- MILLER 1300
- 20 ADAMS 1100
- FORD 3000
- JONES 2975
- SCOTT 3000
- SMITH 800
- 30 ALLEN 1600
- BLAKE 2850
- JAMES 950
- MARTIN 1250
- TURNER 1500
- WARD 1250
- ********** ----------
- sum 29025
- SQL> break on deptno skip 1 on report
- SQL> compute sum label "小计:" of sal on deptno
- SQL> compute sum label "总计:" of sal on report
- select deptno,ename,sal from scott.emp order by deptno;
- SQL>
- DEPTNO ENAME SAL
- ---------- ---------- ----------
- 10 CLARK 2450
- KING 5000
- MILLER 1300
- ********** ----------
- 小计: 8750
- 20 JONES 2975
- FORD 3000
- ADAMS 1100
- SMITH 800
- SCOTT 3000
- ********** ----------
- 小计: 10875
- 30 WARD 1250
- TURNER 1500
- ALLEN 1600
- JAMES 950
- BLAKE 2850
- MARTIN 1250
- ********** ----------
- 小计: 9400
- ----------
- 总计: 29025
Formatting SQL*Plus Reports : http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch6.htm