cube与rollup学习总结
- 格式：docx
- 大小：14.67 KB
- 文档页数：2
分析函数CUBE与ROLLUP学习总结
CUBE: 有2的N次方种组合:
SELECT EMPNO,ENAME,JOB,
SUM(SAL),
GROUPING(EMPNO) GEMPNO,
GROUPING(ENAME) GENAME,
GROUPING(JOB) GJOB
FROM SCOTT.EMP GROUP BY CUBE(EMPNO,ENAME,JOB)
ORDER BY GEMPNO,GENAME,GJOB;
等效于：以上SQL总共有8种组合
EMPNO,ENAME,JOB
EMPNO,ENAME,NULL
EMPNO,NULL,JOB
EMPNO,NULL,NULL
NULL,ENAME,JOB
NULL,ENAME,NULL
NULL,NULL,JOB
NULL,NULL,NULL
SELECT EMPNO,ENAME,JOB,0 GEMPNO,0 GENAME,0 GJOB, SUM(SAL) FROM SCOTT.EMP GROUP BY
EMPNO,ENAME,JOB
UNION ALL
SELECT EMPNO,ENAME,NULL,0 GEMPNO,0 GENAME,1 GJOB ,SUM(SAL) FROM SCOTT.EMP GROUP BY
EMPNO,ENAME
UNION ALL
SELECT EMPNO,NULL,JOB,0 GEMPNO,1 GENAME,0 GJOB ,SUM(SAL) FROM SCOTT.EMP GROUP BY
EMPNO,JOB
UNION ALL
SELECT EMPNO,NULL,NULL,0 GEMPNO,1 GENAME,1 GJOB,SUM(SAL) FROM SCOTT.EMP GROUP BY
EMPNO
UNION ALL
SELECT NULL,ENAME,JOB,1 GEMPNO,0 GENAME,0 GJOB,SUM(SAL) FROM SCOTT.EMP GROUP BY
ENAME,JOB
UNION ALL
SELECT NULL,ENAME,NULL,1 GEMPNO,0 GENAME,1 GJOB,SUM(SAL) FROM SCOTT.EMP GROUP BY
ENAME
UNION ALL
SELECT NULL,NULL,JOB,1 GEMPNO,1 GENAME,0 GJOB,SUM(SAL) FROM SCOTT.EMP GROUP BY JOB
UNION ALL
SELECT NULL,NULL,NULL,1 GEMPNO,1 GENAME,1 GJOB,SUM(SAL) FROM SCOTT.EMP
ORDER BY GEMPNO,GENAME,GJOB;
ROLLUP:有2*N种组合，从左到右的方法
SELECT EMPNO,ENAME,JOB,
SUM(SAL),
GROUPING(EMPNO) GEMPNO,
GROUPING(ENAME) GENAME,
GROUPING(JOB) GJOB
FROM SCOTT.EMP GROUP BY ROLLUP(EMPNO,ENAME,JOB)
ORDER BY GEMPNO,GENAME,GJOB;
等效于：以上SQL总共有4种组合
EMPNO,ENAME,JOB
EMPNO,ENAME,NULL
EMPNO,NULL,NULL
NULL,NULL,NULL
SELECT EMPNO,ENAME,JOB,0 GEMPNO,0 GENAME,0 GJOB, SUM(SAL) FROM SCOTT.EMP GROUP BY
EMPNO,ENAME,JOB
UNION ALL
SELECT EMPNO,ENAME,NULL,0 GEMPNO,0 GENAME,1 GJOB ,SUM(SAL) FROM SCOTT.EMP GROUP BY
EMPNO,ENAME
UNION ALL
SELECT EMPNO,NULL,NULL,0 GEMPNO,1 GENAME,1 GJOB,SUM(SAL) FROM SCOTT.EMP GROUP BY
EMPNO
UNION ALL
SELECT NULL,NULL,NULL,1 GEMPNO,1 GENAME,1 GJOB,SUM(SAL) FROM SCOTT.EMP
ORDER BY GEMPNO,GENAME,GJOB;
