Ø 簡介
在前面的 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 運算符，表示滿足給出清單中的
所有值。通常用于以下場景：
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 運算符，表示滿足給出清單中的
任意值 任意員工的工資的員工姓名、工資
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 |
| > 大于 | 取最大值 | 取最小值 | ||
| < 小于 |
在 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，取出并集
不會去除重複行UNION ALL
可見，兩個結果集中具有兩條相同的記錄，UNION ALL 并沒有去重。
2) INTERSECT
INTERSECT 用于取兩個結果集中相交的記錄，即取得
記錄。還是以剛才的資料為例：
INTERSECT
在兩個查詢的結果集中，存在兩個完全相同的記錄，這就是交集。
3) MINUS
MINUS 中文意思是減去，表示第一個結果集減去第二個結果集，取出它們的
。任然以剛才的資料為例：
SELECT * FROM emp WHERE empno <= 7600
MINUS
SELECT * FROM emp WHERE empno < 7500;
取得兩條記錄，這兩條記錄是在第一個結果集中存在，在第二個結果集中不存在的記錄，則為差集。
使用 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 如下：
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
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
); --這裡隻看文法，不看邏輯
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
);
執行以上語句，将會删除部門名稱不以字母開頭的部門（忽略意義，隻看文法）
6) 對輸出列定義别名
WITH temp(empno2, name2, sal2) AS
(
SELECT empno, ename, sal FROM emp
别名的數量必須與子查詢輸出的列數量相同。
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 結果集需要進行多次處理時，可以減少查詢次數和提高可讀性。
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]的所有上司
START WITH empno = 7876
CONNECT BY PRIOR mgr = empno --表示根據目前 mgr 的值，去尋找 empno 為該值的記錄
根據目前記錄向上遞歸
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]的族兄節點
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
CONNECT BY mgr = PRIOR empno;
6) 使用 connect_by_root 檢視跟節點
SELECT level AS lv, (
connect_by_rootename) root_ename, empno, ename, mgr, PRIOR ename FROM emp
START WITH mgr IS NULL
7) 使用 connect_by_isleaf 檢視是否為葉子節點
SELECT level AS lv, empno, ename, mgr, connect_by_isleaf AS isleaf FROM emp
8) 使用 connect_by_iscycle 檢視是否循環依賴（因為 empno 是主鍵，不可能重複，就不示範了）
SELECT level AS lv, empno, ename, mgr,
connect_by_iscycleas cycle FROM emp
CONNECT BY
NOCYCLEmgr = PRIOR empno;
之前在使用 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) 查詢每個部門第一個入職的員工，部門沒有員工顯示空
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
1) 合并員工表中的所有姓名字段
SELECT WM_CONCAT(ename) AS employees FROM emp;
SELECT LISTAGG(ename) WITHIN GROUP (ORDER BY
ename) AS employees FROM emp;
2) 根據部門，合并員工姓名字段
SELECT deptno, WM_CONCAT(ename) AS employees FROM emp GROUP BY deptno;
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY
empno) AS employees FROM emp GROUP BY deptno;
n 注意
1. 預設情況下，WM_CONCAT() 函數隻有在 11g 或以下版本才支援，12c 版本需要使用 LISTAGG() 函數；
2. 而 LISTAGG() 函數這兩個版本都支援，而且 LISTAGG() 函數可以指定其它
分隔符 排序字段。LISTAGG() 函數沒有版本限制、更靈活，
推薦使用