/*cube比rollup比较,ROLLUP是按列的顺序从右到左递减分组统计,而cube是各种可能性的分组*/
--------------------
-----------------------
/*部分cube分组,group by a.dname,CUBE(b.job),
这样的写法,比group by CUBE(a.dname,b.job)少去了group by b.job和group by null的分组*/
SELECT a.dname,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY CUBE(a.dname,b.job);
--------------------
SELECT a.dname,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY a.dname,b.job
UNION
SELECT a.dname,NULL,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY a.dname
UNION
SELECT NULL,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY b.job
UNION
SELECT NULL,NULL,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY NULL
-----------------------
/*部分cube分组,group by a.dname,CUBE(b.job),
这样的写法,比group by CUBE(a.dname,b.job)少去了group by b.job和group by null的分组*/
SELECT a.dna