oracle 数据库传入集合,Oracle数据库之集合运算

集合运算符

集合运算符查询组合两个组件查询的结果到一个结果，包含集合运算符的查询称为复合查询。sql集合运算符如下：

集合运算符

UNION：All distinct rows selected by either query

UNION ALL：All rows selected by either query,including all duplicates

INTERSECT：All distinct rows selected by both queries

MINUS：All distinct rows selected by the first query but not the second

sql> 查询10和20号部门的员工

sql> 1. select * from emp where deptno in (10,20);

sql> 2. select * from emp where deptno=10 or deptno=20;

sql> 3. 集合运算

sql> select * from emp where deptno=10

sql> 加上

sql> select * from emp where deptno=20

sql> select * from emp where deptno=10

2 union

3 select * from emp where deptno=20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

sql> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 CLERK 1900

20 ANALYST 6000

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DEPTNO JOB SUM(SAL)

---------- --------- ----------

30 9400

29025

sql> 注意的问题

sql> 1. 参与运算的各个集合必须列数相同，且类型一致

sql> 2. 采用第一个集合作为最后的表头

sql> 3. order by永远在最后

sql> 4. 括号

sql> select deptno,sum(sal) from emp group by deptno,job

2 union

3 select deptno,to_char(null),sum(sal) from emp group by deptno

4 union

5 select to_number(null),sum(sal) from emp;

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DEPTNO JOB SUM(SAL)

---------- --------- ----------

30 9400

29025

测试集合运算和聚合函数的效率

sql> –打开sql执行时间的开关

sql> set timing on

sql> select deptno,job);

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 CLERK 1900

20 ANALYST 6000

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DEPTNO JOB SUM(SAL)

---------- --------- ----------

30 9400

29025 ***已用时间: 00: 00: 00.02***

sql> select deptno,sum(sal) from emp;

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DEPTNO JOB SUM(SAL)

---------- --------- ----------

30 9400

29025

***已用时间: 00: 00: 00.08***

//关闭时间开关

sql> set timing off

结论：尽量少使用集合运算。