如何在查询多列的同时，汇总其中的一列或几列

Question

如何在列出多列员工薪资明细的同时，汇总各部门和全部员工的薪资我的测试表是Oracle自带的Scott.emp和Scott.dept两个表。
请问如何实现如下查询，谢谢。部门编号    部门名称  员工编号  员工姓名    工种      工资
------------------------------------------------------------------------------------
10      ACCOUNTING    7782       CLARK     MANAGER   2450.00        
10      ACCOUNTING    7839       KING      PRESIDENT 5000        
10      ACCOUNTING    7934       MILLER    CLERK     1300        
小计                                                 8750
20      RESEARCH      7369       SMITH     CLERK     800.00        
20      RESEARCH      7566       JONES     MANAGER   2975        
20      RESEARCH      7788       SCOTT     ANALYST   3000        
20      RESEARCH      7876       ADAMS     CLERK     1100       
20      RESEARCH      7902       FORD      ANALYST   3000        
小计                                                 10875
30      SALES         7900       JAMES     CLERK     950.00        
30      SALES         7499       ALLEN     SALESMAN  1600            
30      SALES         7521       WARD      SALESMAN  1250.00        
30      SALES         7654       MARTIN    SALESMAN  1250.00        
30      SALES         7698       BLAKE     MANAGER   2850        
30      SALES         7844       TURNER    SALESMAN  1500        
小计                                                 9400
总计                                                 29025 测试数据如下：
create table DEPT
(
  DEPTNO NUMBER(2) not null,
  DNAME  VARCHAR2(14),
  LOC    VARCHAR2(13)
);
insert into dept('10','ACCOUNTING','NEW YORK');
insert into dept('20','RESEARCH','DALLAS');
insert into dept('30','SALES','CHICAGO');
insert into dept('40','OPERATIONS','BOSTON');create table EMP
(
  EMPNO    NUMBER(4) not null,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
);
insert into emp(empno,ename,job,sal,deptno) values('7369','SMITH','CLERK',800.00,20);
insert into emp(empno,ename,job,sal,deptno) values('7499','ALLEN','SALESMAN',1600.00,30);
insert into emp(empno,ename,job,sal,deptno) values('7521','WARD','SALESMAN',1250.00,30);
insert into emp(empno,ename,job,sal,deptno) values('7566','JONES','MANAGER',2975.00,20);
insert into emp(empno,ename,job,sal,deptno) values('7654','MARTIN','SALESMAN',1250.00,30);
insert into emp(empno,ename,job,sal,deptno) values('7698','BLAKE','MANAGER',2850.00,30);
insert into emp(empno,ename,job,sal,deptno) values('7782','CLARK','MANAGER',2450.00,10);
insert into emp(empno,ename,job,sal,deptno) values('7788','SCOTT','ANALYST',3000.00,20);
insert into emp(empno,ename,job,sal,deptno) values('7839','KING','PRESIDENT,'5000.00,10);
insert into emp(empno,ename,job,sal,deptno) values('7844','TURNER','SALESMAN',1500.00,30);
insert into emp(empno,ename,job,sal,deptno) values('7876','ADAMS','CLERK',1100.00,20);
insert into emp(empno,ename,job,sal,deptno) values('7900','JAMES','CLERK',950.00,30);
insert into emp(empno,ename,job,sal,deptno) values('7902','FORD','ANALYST',3000.00,20);
insert into emp(empno,ename,job,sal,deptno) values('7934','MILLER','CLERK',1300.00,10);

Answer 1

SQL> SELECT decode(grouping_id(t2.deptno,ROWNUM),3,'总计',1,'小计',t2.deptno) deptno,
  2         decode(GROUPING(ROWNUM), 1, NULL, MAX(t2.dname)) dname,
  3         decode(GROUPING(ROWNUM), 1, NULL, MAX(EMPNO)) EMPNO,
  4         decode(GROUPING(ROWNUM), 1, NULL, MAX(ENAME)) ENAME,
  5         decode(GROUPING(ROWNUM), 1, NULL, MAX(job)) job,
  6         SUM(t1.sal)
  7  FROM   emp t1, dept t2
  8  WHERE  t1.deptno = t2.deptno
  9  GROUP  BY ROLLUP(t2.deptno, ROWNUM)
 10  /DEPTNO     DNAME                EMPNO      ENAME      JOB       SUM(T1.SAL)
---------- -------------------- ---------- ---------- --------- -----------
10         ACCOUNTING           7782       CLARK      MANAGER          2450
10         ACCOUNTING           7839       KING       PRESIDENT        5000
10         ACCOUNTING           7934       MILLER     CLERK            1300
小计                                                                   8750
20         RESEARCH             7369       SMITH      CLERK             800
20         RESEARCH             7876       ADAMS      CLERK            1100
20         RESEARCH             7902       FORD       ANALYST          3000
20         RESEARCH             7788       SCOTT      ANALYST          3000
20         RESEARCH             7566       JONES      MANAGER          2975
小计                                                                  10875
30         SALES                7499       ALLEN      SALESMAN         1600
30         SALES                7698       BLAKE      MANAGER          2850
30         SALES                7654       MARTIN     SALESMAN         1250
30         SALES                7900       JAMES      CLERK             950
30         SALES                7844       TURNER     SALESMAN         1500
30         SALES                7521       WARD       SALESMAN         1250
小计                                                                   9400
总计                                                                  29025

Answer 2

分析函数比较好 rollup,cube和grouping函数