首页 > 代码库 > oracle 集合运算符

oracle 集合运算符

2024-08-09 22:13:47   221人阅读

技术分享

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 deptno10 union11 select sum(sal) from emp group by null12  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            1010     10 PRESIDENT          1011     10                    3012     20 CLERK              2013     20 ANALYST            2014     20 MANAGER            2015     20                    6016     30 CLERK              3017     30 MANAGER            3018     30 SALESMAN          12019     30                   18020                          27021  22 13 rows selected23  24 SQL> select deptno,job,sum(sal) from emp group by deptno,job 25   2  union26   3  select deptno,to_char(null),sum(sal) from emp group by deptno 27   4  union28   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           140033         10 MANAGER         245034         10 PRESIDENT       500035         10                 885036         20 ANALYST         300037         20 CLERK            80038         20 MANAGER         297539         20                 677540         30 CLERK            95041         30 MANAGER         285042         30 SALESMAN        560043         30                 940044                           2502545  46 13 rows selected

 

oracle 集合运算符


联系
我们