GROUPING SETS,ROLLUP,CUBE用法介绍

准备：

-- 按职位分组

SELECT job,
       AVG(TO_NUMBER(sal))
  FROM emp
 GROUP BY job ;

结果：

CLERK	1037.5
SALESMAN	1400
PRESIDENT	5000
MANAGER	2758.333
ANALYST	3000

--

-- 按部门分组

SELECT deptno,
       AVG(TO_NUMBER(sal))
  FROM emp
 GROUP BY deptno ;

结果如下：

30	1566.667
20	2175
10	2916.667

--
-- 按部门，职位分组统计

SELECT deptno,
       job,
       AVG(TO_NUMBER(sal))
  FROM emp
 GROUP BY deptno,job
 ORDER BY deptno ; 

结果如下：

10	CLERK	1300
10	MANAGER	2450
10	PRESIDENT	5000
20	ANALYST	3000
20	CLERK	950
20	MANAGER	2975
30	CLERK	950
30	MANAGER	2850
30	SALESMAN	1400

---------------------------------------------------------------------------------------------------

接下来就分别介绍一下GROUPING SETS,ROLLUP,CUBE的用法

-- GROUPING SETS用法

-- GROUPING SETS综合不同字段单独进行分组的结果。如上例就是汇总按deptno和job分组统计emp表的平均工资情况

SELECT deptno,
                 job,
        AVG(TO_NUMBER(sal))
    FROM emp
  GROUP BY GROUPING SETS(deptno,job) ;

结果如下：

	CLERK	1037.5
	SALESMAN	1400
	PRESIDENT	5000
	MANAGER	2758.333
	ANALYST	3000
30		1566.667
20		2175
10		2916.667

--

--  ROLLUP 用法

-- ROLLUP 在原有的统计结果的基础上进行横向汇总。上例中先按部门，职位分组统计各部门中各职位的平均工资，然后再统计各部门的平均工资。

SELECT deptno,
       job,
       AVG(TO_NUMBER(sal))
  FROM emp
 GROUP BY ROLLUP(deptno,job) ; 

-- 结果如下:

10	CLERK	1300
10	MANAGER	2450
10	PRESIDENT	5000
10		2916.667
20	CLERK	950
20	ANALYST	3000
20	MANAGER	2975
20		2175
30	CLERK	950
30	MANAGER	2850
30	SALESMAN	1400
30		1566.667
		2073.214

-- 

统计出如下图所示：

--

-- CUBE用法介绍

-- CUBE会在ROLLUP的基础上再进行纵向汇总。如下面的例子中，先按部门，职位分组统计各部门各职位的平均工资，然后再统计各部门和各职位的平均工资。

SELECT deptno,
       job,
       AVG(TO_NUMBER(sal))
  FROM emp
 GROUP BY CUBE(deptno,job);

结果如下：

		2073.214
	CLERK	1037.5
	ANALYST	3000
	MANAGER	2758.333
	SALESMAN	1400
	PRESIDENT	5000
10		2916.667
10	CLERK	1300
10	MANAGER	2450
10	PRESIDENT	5000
20		2175
20	CLERK	950
20	ANALYST	3000
20	MANAGER	2975
30		1566.667
30	CLERK	950
30	MANAGER	2850
30	SALESMAN	1400

统计如下图所示：