【Oracle】rollup函数

当我们在做报表统计的时候，很多时候需要用到‘合计’这个功能，比如我们想得到如下格式的报表：

这张表是按照deptno分组，然后按照deptno分组合计。rollup函数可以完美的解决这个问题。

1.建立一张t表：

create table T

(

  empno  NUMBER(4),

  ename  VARCHAR2(10),

  job    VARCHAR2(9),

  sal    NUMBER(7,2),

  comm   NUMBER(7,2),

  deptno NUMBER(2)

);

2.插入数据：

insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO)

values (7844, 'TURNER', 'SALESMAN', 1500.00, 0.00, 30);

 

insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO)

values (7499, 'ALLEN', 'SALESMAN', 1600.00, 300.00, 30);

 

insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO)

values (7521, 'WARD', 'SALESMAN', 1250.00, 500.00, 30);

 

insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO)

values (7566, 'JONES', 'MANAGER', 2975.00, 100.00, 20);

 

insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO)

values (7654, 'MARTIN', 'SALESMAN', 1250.00, 1400.00, 30);

 

insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO)

values (7698, 'BLAKE', 'MANAGER', 2850.00, 100.00, 30);

 

insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO)

values (7782, 'CLARK', 'MANAGER', 2450.00, 100.00, 10);

 

insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO)

values (7788, 'SCOTT', 'ANALYST', 3000.00, 100.00, 20);

 

insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO)

values (7839, 'KING', 'PRESIDENT', 5000.00, 100.00, 10);

 

insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO)

values (7876, 'ADAMS', 'CLERK', 1100.00, 100.00, 20);

 

insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO)

values (7900, 'JAMES', 'CLERK', 950.00, 100.00, 30);

 

insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO)

values (7902, 'FORD', 'ANALYST', 3000.00, 100.00, 20);

 

insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO)

values (7934, 'MILLER', 'CLERK', 1300.00, 100.00, 10);

commit;

3.使用rollup函数

select empno, ename, sum(sal) as sal, sum(comm) as comm, deptno

from t

group by rollup(deptno, (empno, ename))

having grouping(deptno) = 0;