Oracle——集合运算

集合运算

示例 1：查询10和20号部门的员工

1. select * from emp where deptno=10 or deptno=20;
2. select * from emp where deptno in (10,20);
3. 集合运算
    select * from emp where deptno=10
    union
    select * from emp where deptno=20;

示例2：group by中的rollup函数

　　如果是Group by  ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

      如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。

SELECT
	deptno,
	job,
	sum( sal ) 
FROM
	emp 
GROUP BY
	rollup ( deptno, job );
	
-- 相当于
 
select deptno,job,sum(sal) from emp group by deptno,job
union
select deptno,**to_char(null)**,sum(sal) from emp group by deptno
union
select **to_number(null),to_char(null)**,sum(sal) from emp;

注意的问题：

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

2、采用第一个集合作为最后的表头

3、order by永远在最后

4、括号

SQL优化 4、 尽量不要使用集合运算

集合运算总结：

-- 将t1的结果和t2的结果联合显示。不排序操作，也不去掉重复的行。
select * from t1
union all
select * from t2;
 
-- 将t1的结果和t2的结果联合显示。含有排序操作，也去掉重复的行。
select * from t1
union
select * from t2;
 
-- 将t1的结果和t2的结果的共有部分显示。含有排序操作，也去掉重复的行。
select * from t1
INTERSECT
select * from t2;
 
-- T1表有，而t2表没有的行，去掉重复的行。
select * from t1
MINUS
select * from t2;

高级分组rollup,cube操作：

rollup分组

  -- 按部门分组
  select deptno,sum(sal) from emp group by deptno;
DEPTNO SUM(SAL)
 
30 9400
 
20 6775
 
10 8750
 
  -- 按部门分组,并求总计
  select deptno,sum(sal) from emp  group by rollup(deptno);
DEPTNO SUM(SAL)
 
10 8750
 
20 6775
 
30 9400
 
       24925
Rollup分组，一次全表扫描
 
  /*
  Group by Rollup(a,b,c,d)的结果集为,共n+1个集
  ​
  Group by a,b,c,d
  Union all
  Group by a,b,c
  Union all
  Group by a,b
  Union all
  Group by a
  Union all
  Group by null
  */
  ​
  select deptno,job,sum(sal) from emp group by rollup(deptno,job);
  ​
  相当于
  ​
  select deptno,job,sum(sal) from emp group by deptno,job
  union all
  select deptno,null,sum(sal) from emp group by deptno
  union all
  select null,null,sum(sal) from emp;

Cube分组

  select deptno,job,grouping(deptno),grouping(job),sum(sal) from emp group by cube(deptno,job);
  ​
  结果集为，2**n个结果集
  ​
  select deptno,job,sum(sal) from emp group by deptno,job
  ​
  union all
  ​
  select deptno,null,sum(sal) from emp group by deptno
  ​
  union all
  ​
  select null,job,sum(sal) from emp group by job
  ​
  union all
  ​
  select null,null,sum(sal) from emp;

树结构的查询start with子句

层次结构的数据查询

-- 从ename='SMITH'开始查，一层一层的查出empno=mgr的人的信息
select empno,ename,mgr from emp start with (ename='SMITH')connect by prior mgr=empno;
 
EMPNO ENAME       MGR
 
---
 
 7369 SMITH            7902
 
 7902 FORD             7566
 
 7566 JONES            7839
 
 7839 KING