集合运算
示例 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;
-- 按部门分组
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