Ø 简介
在前面的 Oracle 查询 SELECT 语句（一） 中介绍了 SELECT 常用的一些基本查询语法，接下来再来看 SELECT 更深入的一些查询功能和技巧，包括以下内容：
1. All 与 Any 运算符
2. 集合操作符(UNION、UNION ALL、INTERSECT、MINUS)
3. WITH AS 的用法
4. CONNECT BY 子句的使用
1. All 与 Any 运算符
1) All 运算符，表示满足给出列表中的所有值。通常用于以下场景：
1. 查出大于30号部门所有员工最高工资的员工姓名、工资
--使用ALL
SELECT ename, sal FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30);
--使用MAX
SELECT ename, sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
2) Any 运算符，表示满足给出列表中的任意值。通常用于以下场景：
1. 查出大于30号部门任意员工的工资的员工姓名、工资
--使用ALL
SELECT ename, sal FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30);
--使用MIN
SELECT ename, sal FROM emp WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno = 30);
n 提示
通过以上示例，可以看到通常情况下，ALL 和 ANY 都可以使用 MAX 和 MIN 去取代，所以这两个运算符一般情况用的不多。分析如下：
| 比较运算符 | All | Any | Max | Min |
| > 大于 | 取最大值 | 取最小值 | 取最大值 | 取最小值 |
| < 小于 | 取最小值 | 取最小值 | 取最大值 | 取最小值 |
2. 集合操作符(UNION、UNION ALL、INTERSECT、MINUS)
在 Oracle 中对多个集合操作，主要使用 UNION、UNION ALL、INTERSECT、MINUS 这四个操作符来完成，解决取多个集合的并集、交集和差集。
1) UNION、UNION ALL
UNION 和 UNION ALL 用于取两个结果集的并集，举例说明：
Ø UNION，取出并集并去除重复行，例如：
SELECT * FROM emp WHERE empno <= 7500
UNION
SELECT * FROM emp WHERE empno < 7600;
说明：
1. 两个查询的结果集中，都有两条记录小于7500，UNION 在合并时进行去重了，所以相同的记录只会显示一行。
2. UNION 逻辑上可以合并任意个结果集，所以只需要在后面接着写 UNION 即可。
Ø UNION ALL，取出并集不会去除重复行，例如：
SELECT * FROM emp WHERE empno <= 7500
UNION ALL
SELECT * FROM emp WHERE empno < 7600;
可见，两个结果集中具有两条相同的记录，UNION ALL 并没有去重。
2) INTERSECT
INTERSECT 用于取两个结果集中相交的记录，即取得交集记录。还是以刚才的数据为例：
SELECT * FROM emp WHERE empno <= 7500
INTERSECT
SELECT * FROM emp WHERE empno < 7600;
![clip_image006[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194833921-37635439.jpg "clip_image006[1]")
在两个查询的结果集中，存在两个完全相同的记录，这就是交集。
3) MINUS
MINUS 中文意思是减去，表示第一个结果集减去第二个结果集，取出它们的差集。任然以刚才的数据为例：
SELECT * FROM emp WHERE empno <= 7600
MINUS
SELECT * FROM emp WHERE empno < 7500;
![clip_image007[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194834314-1085668369.png "clip_image007[1]")
取得两条记录，这两条记录是在第一个结果集中存在，在第二个结果集中不存在的记录，则为差集。
3. WITH AS 的用法
使用 WITH AS 语句可以为一个子查询语句块定义一个名称，使用这个子查询名称可以在查询语句的很多地方引用这个子查询。Oracle 数据库像对待内联视图或临时表一样对待它，从而起到一定的优化作用。with 子句是9i中新增的语法。
语法：
WITH <alias_name1> AS (subquery_sql_statement)
[,<alias_name2> AS (subquery_sql_statement)]
[...,<alias_name3> AS (subquery_sql_statement)]
SELECT <column_name_list> FROM <alias>;
n WITH AS 的优点：
1) 增强 SQL 的可读性，比如对 with 子查询取一个有意义的名字等；
2) with 子查询只执行一次，将结果存储在用户临时表空间中，可以被引用多次，增强性能。
n 示例
1) 查询部门总薪水大于所有部门平均薪水的部门
分析（三步骤）：
1. 获取每个部门总薪水；
2. 再计算出所有部门的平均薪水；
3. 最后获取部门总薪水比较部门平均薪水；
SQL 如下：
WITH dept_total AS
(SELECT t1.dname, SUM(t2.sal) totalsal
FROM dept t1, emp t2
WHERE t1.deptno = t2.deptno
GROUP BY t1.dname)
SELECT t.dname, t.totalsal, (SELECT AVG(totalsal) FROM dept_total) avgsal
FROM dept_total t
WHERE t.totalsal > (SELECT AVG(totalsal) FROM dept_total);
![clip_image008[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194834675-2087929215.png "clip_image008[1]"){kind=small}
2) 查询部门总薪水大于所有部门平均薪水的部门（进一步优化）
可以看到之前求平均薪资时，执行了两次 SELECT 语句，其实我们还可以进一步优化，即再定义一个子查询，SQL 如下：
WITH dept_total AS
(SELECT t1.dname, SUM(t2.sal) totalsal
FROM dept t1, emp t2
WHERE t1.deptno = t2.deptno
GROUP BY t1.dname),
dept_avg AS
(SELECT SUM(totalsal) / COUNT(*) avgsal FROM dept_total) --这里模拟复杂处理
SELECT t.dname, t.totalsal, (SELECT avgsal FROM dept_avg) avgsal
FROM dept_total t
WHERE t.totalsal > (SELECT avgsal FROM dept_avg);
![clip_image009[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194835043-1371729519.png "clip_image009[1]"){kind=small}
3) 使用 WITH 子句插入数据
INSERT INTO dept (deptno, dname, loc)
WITH temp AS(
--这里模拟数据来源
SELECT 50 col1, '财务部' col2, '上海' col3 FROM dual UNION
SELECT 60 col1, '运营部' col2, '北京' col3 FROM dual UNION
SELECT 70 col1, '研发部' col2, '武汉' col3 FROM dual
)
SELECT * FROM temp;
查询结果：SELECT * FROM dept;
![clip_image010[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194835435-1394539616.png "clip_image010[1]")
4) 使用 WITH 子句更新数据
UPDATE dept t1 SET loc = (
WITH t2 AS (SELECT * FROM dept)
SELECT concat(loc, '市') col1 FROM t2 WHERE t1.deptno = t2.deptno
); --这里只看语法，不看逻辑
查询结果：SELECT * FROM dept;
![clip_image011[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194835805-1430400038.png "clip_image011[1]")
5) 使用 WITH 子句删除数据
DELETE FROM dept t1 WHERE EXISTS(
WITH t2 AS (SELECT deptno, ascii(substr(dname, 1, 1)) col1 FROM dept),
t3 AS (SELECT * FROM t2 WHERE NOT(col1 >= 65 AND col1 <= 90) AND NOT(col1 >= 97 AND col1 <= 122))
SELECT 1 FROM t3 WHERE t1.deptno = t3.deptno
);
说明：执行以上语句，将会删除部门名称不以字母开头的部门（忽略意义，只看语法）
查询结果：SELECT * FROM dept;
![clip_image012[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194836157-32924610.png "clip_image012[1]")
6) 对输出列定义别名
WITH temp(empno2, name2, sal2) AS
(
SELECT empno, ename, sal FROM emp
)
SELECT * FROM temp;
![clip_image013[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194836500-182705833.png "clip_image013[1]")
注意：别名的数量必须与子查询输出的列数量相同。
n 注意事项：
1) 定义了 with 子查询，可以在之后的 SELECT 中不被使用到，并不会报错，例如：
WITH temp_emp AS
(SELECT * FROM emp WHERE sal > 3000)
SELECT * FROM emp WHERE sal > 3000;
提示：有些文章中提到以上情况下会报错，那可能是 11g 以下版本，经测试在 11g 版本中是不报错的。
2) 在 with 中定义多个子查询时，后面的子查询语句可以引用前面的子查询的别名；
3) with 子句通常用于对某一 SELECT 结果集需要进行多次处理时，可以减少查询次数和提高可读性。
4. CONNECT BY 子句的使用
connect by 子句通常用于数据表存在父子、上下级等层级关系的场景中，可以进行层级查询（递归查询）。
语法：
START WITH condition CONNECT BY [NOCYCLE] condition [AND condition]...
START WITH: 指定起始节点的条件；
CONNECT BY: 指定节点之间的关联关系；
PRIOR: 层级关联限定符，可用于显示层级中指定的字段；
NOCYCLE: 是否终止循环依赖，如存在循环依赖时，不指定该参数会报错。指定该参数，循环依赖只会显示一行；
connect_by_iscycle: 是否为循环行，1表示是，0表示否，必须指定 NOCYCLE 参数后才能使用该参数；
connect_by_isleaf: 是否为叶子节点，0表示是，0表示否；
level: 表示层级的级数，值越小表示层级越高，第一层为1，之后开始递增。
n 在 scott 用户中提供了一个 emp（员工表），数据如下：
SELECT * FROM emp ORDER BY mgr DESC;
![clip_image016[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194837429-179654932.png "clip_image016[1]")
数据层次结构如下：
![clip_image018[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194837856-93224431.jpg "clip_image018[1]")
可以看到，该表包含4级别，这是我花了半个小时才整理好的，眼睛都看花了。由此可见，如果人为去找层次结构的数据，是一件多么辛苦的事情。所以，这就是 connect by 子句的强大所在。
好了，废话不多说。现在搞清楚层次结构后，下面开始各种查询：
1) 查出员工编号[7839]下的所有下属
SELECT level, empno, ename, mgr FROM emp
START WITH empno = 7839
CONNECT BY PRIOR empno = mgr --表示根据当前 empno 的值，去寻找 mgr 为该值的记录
ORDER BY level;
![clip_image019[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194838369-310455651.png "clip_image019[1]")
说明：根据当前记录向下递归
2) 查出员工编号[7876]的所有领导
SELECT level, empno, ename, mgr FROM emp
START WITH empno = 7876
CONNECT BY PRIOR mgr = empno --表示根据当前 mgr 的值，去寻找 empno 为该值的记录
ORDER BY level;
![clip_image020[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194838742-1863614425.png "clip_image020[1]")
说明：根据当前记录向上递归
3) 查出员工编号[4902]的领导，以及与领导同级员工
WITH Temp AS (
SELECT level AS lv, empno, ename, mgr, PRIOR ename FROM emp
START WITH mgr IS NULL
CONNECT BY mgr = PRIOR empno
)
SELECT * FROM Temp t1, Temp t2 WHERE t1.empno = 7902 AND t2.lv = (t1.lv - 1) --这里借助了笛卡尔积
AND t2.empno <> t1.mgr; --是否包含父节点
![clip_image022[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194839126-1557808019.jpg "clip_image022[1]")
4) 查出员工编号[7566]的族兄节点
WITH Temp AS (
SELECT level AS lv, empno, ename, mgr, PRIOR ename FROM emp
START WITH mgr IS NULL
CONNECT BY mgr = PRIOR empno
)
SELECT t2.* FROM Temp t1, Temp t2 WHERE t1.empno = 7566
AND t2.lv = t1.lv AND t2.empno <> 7566; --这里借助了笛卡尔积
![clip_image023[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194839491-844313814.png "clip_image023[1]")
5) 使用 level 伪列，格式化输出
SELECT (lpad(' ', level * 2) || ename) ename, empno, mgr, level FROM emp
START WITH empno = 7839
CONNECT BY mgr = PRIOR empno;
6) 使用 connect_by_root 查看跟节点
SELECT level AS lv, (connect_by_root ename) root_ename, empno, ename, mgr, PRIOR ename FROM emp
START WITH mgr IS NULL
CONNECT BY mgr = PRIOR empno;
![clip_image025[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194840172-112681574.png "clip_image025[1]")
7) 使用 connect_by_isleaf 查看是否为叶子节点
SELECT level AS lv, empno, ename, mgr, connect_by_isleaf AS isleaf FROM emp
START WITH mgr IS NULL
CONNECT BY mgr = PRIOR empno;
![clip_image026[1]](https://img2018.cnblogs.com/blog/654920/202001/654920-20200105194840542-1769881373.png "clip_image026[1]")
8) 使用 connect_by_iscycle 查看是否循环依赖（因为 empno 是主键，不可能重复，就不演示了）
SELECT level AS lv, empno, ename, mgr, connect_by_iscycle as cycle FROM emp
START WITH mgr IS NULL
CONNECT BY NOCYCLE mgr = PRIOR empno;