UNION/UNION ALL 并集
INTERSECT 交集
MINUS 差集
我们知道group by 增强中 http://www.cnblogs.com/liuwt365/p/4181256.html
group by rollup(a,b)
=
group by a,b
+
group by a
+
group by null
所以我们应该写sql语句应该为：
1 SQL> select deptno,job,sum(sal) from emp group by deptno,job 2 2 union 3 3 select deptno,sum(sal) from emp group by deptno 4 4 union 5 5 select sum(sal) from emp group by null; 6 7 select deptno,job,sum(sal) from emp group by deptno,job 8 union 9 select deptno,sum(sal) from emp group by deptno 10 union 11 select sum(sal) from emp group by null 12 13 ORA-01789: 查询块具有不正确的结果列数
但是出错了，这是集合运算符的限制
1. 参与运算的各个集合必须列数相同 且类型一致
2. 采用第一个集合的表头作为最后的表头
3. 如果排序，必须在每个集合后使用相同的order by
4. 可以使用括号
1 SQL> select deptno,job,sum(sal) 2 2 from emp 3 3 group by rollup(deptno,job) 4 4 ; 5 6 DEPTNO JOB SUM(DEPTNO) 7 ------ --------- ----------- 8 10 CLERK 10 9 10 MANAGER 10 10 10 PRESIDENT 10 11 10 30 12 20 CLERK 20 13 20 ANALYST 20 14 20 MANAGER 20 15 20 60 16 30 CLERK 30 17 30 MANAGER 30 18 30 SALESMAN 120 19 30 180 20 270 21 22 13 rows selected 23 24 SQL> select deptno,job,sum(sal) from emp group by deptno,job 25 2 union 26 3 select deptno,to_char(null),sum(sal) from emp group by deptno 27 4 union 28 5 select to_number(null),to_char(null),sum(sal) from emp group by null; 29 30 DEPTNO JOB SUM(SAL) 31 ---------- --------- ---------- 32 10 CLERK 1400 33 10 MANAGER 2450 34 10 PRESIDENT 5000 35 10 8850 36 20 ANALYST 3000 37 20 CLERK 800 38 20 MANAGER 2975 39 20 6775 40 30 CLERK 950 41 30 MANAGER 2850 42 30 SALESMAN 5600 43 30 9400 44 25025 45 46 13 rows selected