准备:
-- 按职位分组
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 |
统计如下图所示: