select ename,
sal,
empno,
deptno,
null sumsal,
null sumempno
from emp
union all
select null ename,
null sal,
null empno,
deptno,
sum(sal) sumsal,
sum(empno)sumempno
from emp
group by rollup(deptno)
order by deptno
结果:
ENAME
SALEMPNO DEPTNO SUMSAL SUMEMPNO
---------- ---------- ---------- ---------- ---------- ----------
KING
5000 7839 10
counttotal
10
875023555
CLARK
2450 7782 10
MILLER 1300 7934 10
SCOTT
3000 7788 20
SMITH
800 7369 20
JONES
2975 7566 20
counttotal
20
1087538501
FORD
3000 7902 20
ADAMS
1100 7876 20
JAMES
950 7900 30
ENAME
SALEMPNO DEPTNO SUMSAL SUMEMPNO
---------- ---------- ---------- ---------- ---------- ----------
TURNER 1500 7844 30
BLAKE
2850 7698 30
MARTIN 1250 7654 30
WARD
1250 7521 30
ALLEN
1600 7499 30
counttotal
30
940046116
counttotal
29025 108172
为什么不是这样:
ENAME
SALEMPNO DEPTNO SUMSAL SUMEMPNO
---------- ---------- ---------- ---------- ---------- ----------
KING
5000 7839 10
CLARK
2450 7782 10
MILLER 1300 7934 10
counttotal
10
875023555
SCOTT
3000 7788 20
SMITH
800 7369 20
JONES
2975 7566 20
FORD
3000 7902 20
ADAMS
1100 7876 20
counttotal
20
1087538501
JAMES
950 7900 30
ENAME
SALEMPNO DEPTNO SUMSAL SUMEMPNO
---------- ---------- ---------- ---------- ---------- ----------
TURNER 1500 7844 30
BLAKE
2850 7698 30
MARTIN 1250 7654 30
WARD
1250 7521 30
ALLEN
1600 7499 30
counttotal
30
940046116
counttotal
29025 108172 |
