Oracle資料庫——子查詢-07

子查詢（核心，難點）

子查詢沒有任何固定的文法，因為子查詢是一個綜合體，将之前所學習過的限定查詢，多表查詢，分組統計

查詢融合在一起形成新的查詢方式，而且在開發之中子查詢的使用是最多的，一定會用的

如果非要給子查詢的文法，那麼可以參考如下形式：

SELECT [DISTINCT] * | 列名稱 [别名],...|統計函數|分組字段  

,(SELECT [DISTINCT] * | 列名稱 [别名],...|統計函數|分組字段  

FROM 表名稱 [别名]    

WHERE 限定條件(s)

[GROUP BY 分組字段，分組字段,..]    ==<字段資料有重複

[HAVING 分組後過濾]

[ORDER BY 排序字段[ASC|DESC],排序字段[ASC|DESC],...];  )

FROM 表名稱 [别名]    

WHERE 限定條件(s)

[GROUP BY 分組字段，分組字段,..]    ==<字段資料有重複

SELECT [DISTINCT] * | 列名稱 [别名],...|統計函數|分組字段  

FROM 表名稱 [别名]    

WHERE 限定條件(s),(SELECT [DISTINCT] * | 列名稱 [别名],...|統計函數|分組字段  

FROM 表名稱 [别名]    

WHERE 限定條件(s)

[GROUP BY 分組字段，分組字段,..]    ==<字段資料有重複

[HAVING 分組後過濾]

[ORDER BY 排序字段[ASC|DESC],排序字段[ASC|DESC],...];  )

[GROUP BY 分組字段，分組字段,..]    ==<字段資料有重複

[HAVING 分組後過濾]

[ORDER BY 排序字段[ASC|DESC],排序字段[ASC|DESC],...];  

[HAVING 分組後過濾]

[ORDER BY 排序字段[ASC|DESC],排序字段[ASC|DESC],...];  

理論上子查詢可以出現在一條SQL語句的任意位置之中，但是必須使用“()”聲明，而一般在開發之中以下

幾個子句出現子查詢是比較常見

• WHERE子句：子查詢一般傳回單行單列，單行多列，多行單列資料：
• HAVING子句：子查詢傳回單行單列而且要使用統計函數；
• FROM子句：子查詢傳回多行多列資料
• 在WHERE子句之中使用子查詢

WHERE子句之中出現的在查詢表示子查詢傳回的單行單列，多行單列，單行多列三種資料

1.子查詢傳回單行單列

範例：查出公司工資最低的雇員資訊

第一步：找到公司的最低工資，依靠MIN（）函數統計，不需要GROUP BY

SELECT MIN(sal) FROM emp;

第二步：以上的查詢傳回有單行單列的結構

SELECT * FROM emp

WHERE sal=(SELECT MIN(sal) FROM emp);

範例：查出公司雇傭最早的雇員資訊

第一步：找出最早的雇員日期是多少

SELECT MIN(hiredate) FROM emp;

第二步：以上的查詢傳回有單行單列的資料，根據需求肯定在WHERE之中使用

SELECT * FROM emp

WHERE hiredate=(SELECT MIN(hiredate) FROM emp);

2.子查詢傳回單行多列（了解）

範例：查詢出與SCOTT工作相同，工資相同的雇員資訊

第一步，找到SCOTT的工作和工資

SELECT job,sal FROM emp WHERE ename='SCOTT';

第二步：以上的結果傳回的單行二列的資料

SELECT * FROM emp

WHERE (job,sal)=(

SELECT job,sal FROM emp WHERE ename='SCOTT') AND ename<>'SCOTT';

範例：查詢公司雇傭日期最早，工資最低的雇員

第一步：統計出公司的最早雇傭日期和最低工資

SELECT MIN(hiredate),MIN(sal) FROM emp;

第二步：以上查詢傳回單行多列，在WHERE中使用

SELECT * FROM emp

WHERE (hiredate,sal)=(SELECT MIN(hiredate),MIN(sal) FROM emp);

3.子查詢傳回多行單列資料

在之前使用過IN查詢，IN查詢是規定了一個範圍，那麼如果子查詢傳回的是多行單列資料，實際上也是

一個範圍，例如：

範例：查詢所有經理的工資

SELECT sal  FROM emp WHERE job='MANAGER';

這個時候是多行單列資料，是以它就是一個資料的參考範圍，而對于資料庫的參考範圍可以使用三種

符号進行判斷：IN,ANY,ALL

(一)IN操作

此操作與之前講解過的IN使用形式類似

SELECT sal  FROM emp WHERE job='MANAGER';

SELECT * FROM emp

WHERE sal IN (SELECT sal  FROM emp WHERE job='MANAGER');

但是需要注意的是，如果此時使用的是NOT IN，子查詢如果有null值傳回，則不會有任何的查詢結果傳回

SELECT * FROM emp

WHERE comm NOT IN(SELECT comm FROM emp);

(二)ANY操作

對于ANY操作表示的也是子查詢中的每一個結果進行比較，而ANY有三種使用方式

>ANY:比子查詢之中傳回最小的值要大

SELECT * FROM emp

WHERE sal>ANY (SELECT sal  FROM emp WHERE job='MANAGER');

<ANY：比子查詢之中傳回最大的值要小

SELECT * FROM emp

WHERE sal<ANY (SELECT sal  FROM emp WHERE job='MANAGER');

=ANY：與IN判斷的功能完全相同

SELECT * FROM emp

WHERE sal=ANY (SELECT sal  FROM emp WHERE job='MANAGER');

（三）ALL操作

對于ALL也有兩種使用方式

>ALL:比子查詢之中傳回最大的值要大

SELECT * FROM emp

WHERE sal>ALL(SELECT sal  FROM emp WHERE job='MANAGER');

<ALL：比子查詢之中傳回最小的值要小

SELECT * FROM emp

WHERE sal<ALL(SELECT sal  FROM emp WHERE job='MANAGER');

至于工作之中使用那個，請根據具體的業務需求來決定

在HAVING中使用子查詢

HAVING一定是在GROUP BY之後使用的，表示對分組後的資料進行再次的過濾。如果子查詢出現在HAVING

之中，則表示要使用統計函數，而此時的子查詢肯定傳回的是單行單列資料。

範例：統計出每個部門的編号，平均工資，并且要求這些部門的平均工資高于公司平均工資

第一步：統計出公司的平均工資

SELECT AVG(sal)FROM emp;

第二步：根據需求需要統計查詢

SELECT deptno,AVG(sal)

FROM emp

GROUP BY deptno

HAVING AVG(sal)>(SELECT AVG(sal)FROM emp);

如果沒有統計，那麼就沒有HAVING

在SELECT子句中使用子查詢（沒用）

在SELECT之中使用子查詢的情況很少見

範例：查詢雇傭編号是7369的姓名，職位，工資，部門名稱

SELECT ename,job,sal,

(SELECT d.dname FROM dept d WHERE d.deptno=e.deptno)

FROM emp e

WHERE empno=7369;

這種查詢非常可怕，會帶來”1+N“此查詢問題，例如：現在需求變更，查詢出每個雇員的編号，姓名，

職位，部門名稱，如果采用以上的代碼怎麼改昵？

SELECT e.empno,e.ename,e.job,e.sal,

(SELECT d.dname FROM dept d WHERE d.deptno=e.deptno)

FROM emp e;

這個時候查詢出全部的雇員需要執行1此查詢，而且每一次操作之中，SELECT子句又會查詢dept表

，如果emp傳回有20行記錄，那麼就要查詢20次的dept表

在FROM子句中使用子查詢（核心）

FROM子句主要的功能來确定資料源的，是以如果在FROM子句裡面出現子查詢，子查詢隻會傳回一種結構：

多行多列（臨時表）。

範例：統計出每個部門的編号，名稱，位置，部門人數，平均工資

實作方式一：采用多表關聯查詢統計

SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal)

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno

GROUP BY d.deptno,d.dname,d.loc;

實作方式二：利用子查詢完成

第一步：查詢出每個部門的編号，部門人數，平均工資。隻需要emp一張表就可以解決

SELECT deptno,COUNT(empno),AVG(sal)

FROM emp

GROUP BY deptno;

第二步：以上的查詢傳回的是多行多列資料，這屬于一種表的結構，那麼可以直接在FROM之後編寫，但是

為了友善操作可以為它起一個别名

SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg

FROM dept d,(SELECT deptno dno,COUNT(empno) count,AVG(sal) avg

         FROM emp

             GROUP BY deptno) temp

WHERE d.deptno=temp.dno(+);

讨論：兩種查詢操作使用哪一種更好？

為了更好的分析出使用哪種方式好操作，将表中的資料各自擴大100倍，即：emp表1400條記錄，而dept表

400條記錄，于是進行對比

使用多字段分組資料量：emp表的1400*dept表的400=560000條記錄

使用子查詢統計

子查詢資料量：emp表的1400，最多傳回400行記錄

子查詢與dept表關聯：子查詢的最多400行*dept表的400行=160000行記錄

整體記錄：161400條記錄

是以通過以上的分析一定要清楚，在日後的開發之中，盡量要回避多表查詢，而使用子查詢性能會更好

在FROM之中出現子查詢還有另外一種功能，指的是整個查詢已經無法繼續使用統計函數，但是又必須統計

資訊的情況下，也可以考慮FROM之後的子查詢。