Oracle 查询(SELECT)语句（二）

Ø 简介

在前面的 Oracle 查询 SELECT 语句（一）中介绍了 SELECT 常用的一些基本查询语法，接下来再来看 SELECT 更深入的一些查询功能和技巧，包括以下内容：

1. All 与 Any 运算符

2. 集合操作符(UNION、UNION ALL、INTERSECT、MINUS)

3. WITH AS 的用法

4. CONNECT BY 子句的使用

5. 使用 CROSS APPLY 与 OUTER APPLY 连接查询（需要 12c 或以上版本）

6. 使用 WM_CONCAT() 或 LISTAGG() 函数合并多行字段输出

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;

在两个查询的结果集中，存在两个完全相同的记录，这就是交集。

3) MINUS

MINUS 中文意思是减去，表示第一个结果集减去第二个结果集，取出它们的差集。任然以刚才的数据为例：

SELECT * FROM emp WHERE empno <= 7600

MINUS

SELECT * FROM emp WHERE empno < 7500;

取得两条记录，这两条记录是在第一个结果集中存在，在第二个结果集中不存在的记录，则为差集。

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);

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);

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;

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;

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;

6) 对输出列定义别名

WITH temp(empno2, name2, sal2) AS

(

SELECT empno, ename, sal FROM emp

)

SELECT * FROM temp;

注意：别名的数量必须与子查询输出的列数量相同。

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;

数据层次结构如下：

可以看到，该表包含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;

说明：根据当前记录向下递归

2) 查出员工编号[7876]的所有领导

SELECT level, empno, ename, mgr FROM emp

START WITH empno = 7876

CONNECT BY PRIOR mgr = empno --表示根据当前 mgr 的值，去寻找 empno 为该值的记录

ORDER BY level;

说明：根据当前记录向上递归

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; --是否包含父节点

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; --这里借助了笛卡尔积

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;

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;

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;

5. 使用 CROSS APPLY 与 OUTER APPLY 连接查询（需要 12c 或以上版本）

之前在使用 SQL Server 开发时，也写了一篇文章使用 CROSS APPLY 与 OUTER APPLY 连接查询。道理是想通的，那么在 Oracle 中如何使用呢？语法其实差不多，但是需要 12c 或以上版本才支持，否则 CROSS APPLY 会出现“ORA-00905:缺失关键字”，OUTER APPLY 会出现“ORA-00933:SQL 命令未正确结束”的错误。举例说明：

1) 查询每个部门第一个入职的员工，部门没有员工不显示部门

SELECT t1.deptno, t1.dname, t2.ename, t2.hiredate FROM dept t1

CROSS APPLY(SELECT * FROM (SELECT rownum AS rnum, t.ename, t.hiredate FROM emp t WHERE t1.deptno = t.deptno ORDER BY t.hiredate DESC) t WHERE t.rnum = 1) t2

ORDER BY t1.deptno;

2) 查询每个部门第一个入职的员工，部门没有员工显示空

SELECT t1.deptno, t1.dname, t2.ename, t2.hiredate FROM dept t1

OUTER APPLY(SELECT * FROM (SELECT rownum AS rnum, t.ename, t.hiredate FROM emp t WHERE t1.deptno = t.deptno ORDER BY t.hiredate DESC) t WHERE t.rnum = 1) t2