ORACLE ROLLUP和CUBE的使用
ROLLUP,是GROUP BY子句的一种扩展,可以为每个分组返回小计记录以及为所有分组返回总计记录。
CUBE,也是GROUP BY子句的一种扩展,可以返回每一个列组合的小计记录,同时在末尾加上总计记录。
在文章的最后附上了相关表和记录创建的脚本。
1、向ROLLUP传递一列
SQL> select division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
SQL>
再来看一下如果使用普通的GROUP BY,而没有ROLLUP是个什么情况
SQL> select division_id,sum(salary)
2 from employees2
3 group by division_id
4 order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
可以看到,缺少了最后的统计信息。
2、向ROLLUP传递多列
SQL> select division_id,job_id,sum(salary)
2 from employees2
3 group by rollup(division_id,job_id)
4 order by division_id,job_id;
DIV JOB SUM(SALARY)
--- --- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE 1320000
SAL MGR 4446000
SAL WOR 490000
SAL 4936000
DIV JOB SUM(SALARY)
--- --- -----------
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP 1015000
8881000
16 rows selected.
可以看到,除了在最后有一个求和记录外,每个division_id分组也会有一个求和记录。
那么我们现在交换一下ROLLUP中数据列的顺序,看看结果怎样
SQL> select job_id,division_id,sum(salary)
2 from employees2
3 group by rollup(job_id,division_id)
4 order by job_id,division_id;
JOB DIV SUM(SALARY)
--- --- -----------
ENG OPE 245000
ENG 245000
MGR BUS 530000
MGR OPE 805000
MGR SAL 4446000
MGR SUP 465000
MGR 6246000
PRE BUS 800000
PRE 800000
TEC SUP 115000
TEC 115000
JOB DIV SUM(SALARY)
--- --- -----------
WOR BUS 280000
WOR OPE 270000
WOR SAL 490000
WOR SUP 435000
WOR 1475000
8881000
17 rows selected.
结果类似,只不过是每一个job_id分组有一个求和记录罢了。
3、向CUBE传递一列
SQL> select division_id,sum(salary)
2 from employees2
3 group by cube(division_id)
4 order