以scott.emp表为例进行说明:
一、如果只是统计不同部门不同岗位的平均工资,直接使用group by 子句:
SQL> SELECT deptno,job, avg(sal) from scott.emp group by(deptno,job);
DEPTNO JOB AVG(SAL)
---------- ------------------ ----------
20 CLERK 950
30 SALESMAN 1400
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 3000
9 rows selected.
二、当使用rollup操作时,在生成原有统计结果的基础上,还会生成横向小计结果——部门平均工资:
SQL> SELECT deptno,job, avg(sal) from scott.emp group by rollup(deptno,job);
DEPTNO JOB AVG(SAL)
---------- ------------------ ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 2916.66667
20 CLERK 950
20 ANALYST 3000
20 MANAGER 2975
20 2175
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
DEPTNO JOB AVG(SAL)
---------- ------------------ ----------
30 1566.66667
2073.21429
三、使用cube操作时,在rollup的基础上还会生成岗位平均工资:
SQL> SELECT deptno,job, avg(sal) from scott.emp group by cube(deptno,job);
DEPTNO JOB AVG(SAL)
---------- ------------------ ----------
2073.21429
CLERK 1037.5
ANALYST 3000
MANAGER 2758.33333
SALESMAN 1400
PRESIDENT 5000
10 2916.66667
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 2175
DEPTNO JOB AVG(SAL)
---------- ------------------ ----------
20 CLERK 950
20 ANALYST 3000
20 MANAGER 2975
30 1566.66667
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
18 rows selected.
GROUPING(column_name)
对输入列返回0或1,如果该行数据使用了数据的列中的信息,则输出0,没有用到则输出1
或者这么理解,对于该行得出的统计数据,需要从 输入列中选择数据的话,输出0,不需要选择数据的就输出1
SQL> SELECT deptno,job, avg(sal),grouping(deptno),grouping(job) from scott.emp group by cube(deptno,job);
DEPTNO JOB AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------- ------------------ ---------- ---------------- -------------
2073.21429 1 1
CLERK 1037.5 1 0
ANALYST 3000 1 0
MANAGER 2758.33333 1 0
SALESMAN 1400 1 0
PRESIDENT 5000 1 0
10 2916.66667 0 1
10 CLERK 1300 0 0
10 MANAGER 2450 0 0
10 PRESIDENT 5000 0 0
20 2175 0 1
DEPTNO JOB AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------- ------------------ ---------- ---------------- -------------
20 CLERK 950 0 0
20 ANALYST 3000 0 0
20 MANAGER 2975 0 0
30 1566.66667 0 1
30 CLERK 950 0 0
30 MANAGER 2850 0 0
30 SALESMAN 1400 0 0
18 rows selected.