Oracle 多表查詢(2)

標籤：欄位   dal   man   cin   分享   nvl   員工   理論   觀察   

四、統計函數及分組查詢

1、統計函數

在之前學習過一個COUNT()函數，此函數的功能可以統計出表中的資料量，實際上這個就是一個統計函數，而常用的統計函數有如下幾個：

• COUNT()：查詢表中的資料記錄；

• AVG()：求出平均值；

• SUM()：求和；

• MAX()：求出最大值；

• MIN()：求出最小值；

範例：測試COUNT()、AVG()、SUM()

統計出公司的所有僱員，每個月支付的平均工資及總工資。

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

注意點：關於COUNT()函數

COUNT()函數的主要功能是進行資料的統計，但是在進行資料統計的時候，如果一張表中沒有統計記錄，COUNT()也會返回資料，只是這個資料是“0”。

SELECT COUNT(ename) FROM BONUS;

如果使用的是其他函數，則有可能返回null，但是COUNT()永遠都會返回一個具體的數字，這一點以後在開發之中都會使用到。

2、分組查詢

在講解分組操作之前首先必須先明確一點，什麼情況下可能分組，例如：

• 公司的所有僱員，要求男性一組，女性一組，之後可以統計男性和女性的數量；

• 按照年齡分組，18歲以上的分一組，18歲以下的分一組；

• 按照地區分組：北京人一組，上海人一組，四川一組；

這些資訊如果都儲存了資料庫之中，肯定在資料的某一列上會存在重複的內容，例如：按照性別分組的時候，性別肯定有重複（男和女），按照年齡分組（有一個範圍的重複），按照地區分組有一個地區的資訊重複。

所以分組之中有一個不成文的規定：當資料重複的時候分組才有意義，因為一個人也可以一組（沒什麼意義）。

SELECT [DISTINCT] *|分組欄位1 [別名] [,分組欄位2 [別名] ,…] | 統計函數FROM 表名稱 [別名], [表名稱 [別名] ,…][WHERE 條件(s)][GROUP BY 分組欄位1 [,分組欄位2 ,…]][ORDER BY 排序欄位 ASC | DESC [,排序欄位 ASC | DESC]];

範例：按照部門編號分組，求出每個部門的人數，平均工資

SELECT deptno, COUNT(empno), AVG(sal)FROM empGROUP BY deptno;

範例：按照職位分組，求出每個職位的最高和最低工資

SELECT job, MAX(sal), MIN(sal)FROM empGROUP BY job;

但是現在一旦分組之後，實際上對於文法上就會出現了新的限制，對於分組有以下要求：

• 分組函數可以在沒有分組的時候單獨用使用，可是卻不能出現其他的查詢欄位；

分組函數單獨使用：	SELECT COUNT(empno) FROM emp;
錯誤的使用，出現了其他欄位：	SELECT empno,COUNT(empno) FROM emp;

• 如果現在要進行分組的話，則SELECT子句之後，只能出現分組的欄位和統計函數，其他的欄位不能出現：

正確做法：	SELECT job,COUNT(empno),AVG(sal) FROM emp GROUP BY job;
錯誤的做法：	SELECT deptno,job,COUNT(empno),AVG(sal) FROM emp GROUP BY job;

• 分組函數允許嵌套，但是嵌套之後的分組函數的查詢之中不能再出現任何的其他欄位。

範例：按照職位分組，統計平均工資最高的工資

1、先統計出各個職位的平均工資

SELECT job,AVG(sal)FROM empGROUP BY job;

2、平均工資最高的工資

SELECT MAX(AVG(sal))FROM empGROUP BY job;

範例：查詢出每個部門的名稱、部門的人數、平均工資

1、確定所需要的資料表：

• dept表：每個部門的名稱；

• emp表：統計出部門的人數、平均工資；

2、確定已知的關聯欄位：emp.deptno=dept.deptno；

範例：將dept表和emp表的資料關聯

SELECT d.dname,e.empno,e.salFROM dept d, emp eWHERE d.deptno=e.deptno;

DNAME               EMPNO        SAL-------------- ---------- ----------ACCOUNTING           7782       2450ACCOUNTING           7839       5000ACCOUNTING           7934       1300RESEARCH             7369        800RESEARCH             7876       1100RESEARCH             7902       3000RESEARCH             7788       3000RESEARCH             7566       2975SALES                7499       1600SALES                7698       2850SALES                7654       1250SALES                7900        950SALES                7844       1500SALES                7521       1250已選擇14行。

此時的查詢結果中，可以發現在dname欄位上顯示出了重複的資料，按照之前對分組的理解，只要資料重複了，那麼就有可能進行分組的查詢操作，但是此時與之前的分組不太一樣，之前的分組是針對於一張實體表進行的分組（emp、dept都屬於實體表），但是對於以上的資料是通過查詢結果顯示的，所以是一張臨時的虛擬表，但是不管是否是實體表還是虛擬表，只要是有重複，那麼就直接進行分組。

SELECT d.dname,COUNT(e.empno),AVG(e.sal)FROM dept d, emp eWHERE d.deptno=e.deptnoGROUP BY d.dname;

但是這個分組並不合適，因為部門一共有四個部門（因為現在已經引入了dept表，dept表存在了四個部門的資訊），所以應該通過左右串連改變查詢的結果。

SELECT d.dname,COUNT(e.empno),NVL(AVG(e.sal),0)FROM dept d, emp eWHERE d.deptno=e.deptno(+)GROUP BY d.dname;

之前的所有操作都是針對於單個欄位分組的，而實際上分組操作之中也可以實現多欄位分組。

範例：要求顯示每個部門的編號、名稱、位置、部門的人數、平均工資

1、確定所需要的資料表：

• dept表：每個部門的名稱；

• emp表：統計出部門的人數、平均工資；

2、確定已知的關聯欄位：emp.deptno=dept.deptno；

範例：將emp表和dept表關聯查詢

SELECT d.deptno,d.dname,d.loc,e.empno,e.salFROM dept d,emp eWHERE d.deptno=e.deptno(+);

    DEPTNO DNAME          LOC                EMPNO        SAL---------- -------------- ------------- ---------- ----------        10 ACCOUNTING     NEW YORK            7782       2450        10 ACCOUNTING     NEW YORK            7839       5000        10 ACCOUNTING     NEW YORK            7934       1300        20 RESEARCH       DALLAS              7369        800        20 RESEARCH       DALLAS              7876       1100        20 RESEARCH       DALLAS              7902       3000        20 RESEARCH       DALLAS              7788       3000        20 RESEARCH       DALLAS              7566       2975        30 SALES          CHICAGO             7499       1600        30 SALES          CHICAGO             7698       2850        30 SALES          CHICAGO             7654       1250        30 SALES          CHICAGO             7900        950        30 SALES          CHICAGO             7844       1500        30 SALES          CHICAGO             7521       1250        40 OPERATIONS     BOSTON已選擇15行。

此時存在了重複資料，而且這個重複的資料平均在了三列上（deptno,dname,loc），所以在分組上的GROUP BY子句中就可以寫上三個欄位：

SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),NVL(AVG(e.sal),0)FROM dept d,emp eWHERE d.deptno=e.deptno(+)GROUP BY d.deptno,d.dname,d.loc;

以上就是多欄位分組，但是不管是單欄位還是多欄位，一定要有一個前提，存在了重複資料。

範例：要求統計出每個部門的詳細資料，並且要求這些部門的平均工資高於2000；

在以上程式的基礎上完成開發，在之前唯一所學習的限定查詢的文法只有WHERE子句，所以下面先使用WHERE完成要求。

SELECT d.deptno,d.dname,d.loc,COUNT(e.empno) mycount,NVL(AVG(e.sal),0) myavgFROM dept d,emp eWHERE d.deptno=e.deptno(+) AND AVG(e.sal)>2000GROUP BY d.deptno,d.dname,d.loc;

現在出現了如下的錯誤提示：

WHERE d.deptno=e.deptno(+) AND AVG(e.sal)>2000                               *第 3 行出現錯誤:ORA-00934: 此處不允許使用分組函數

本錯誤提示的核心意思就是在WHERE子句之中不能使用統計函數，之所以在WHERE子句之中不能使用，實際上跟WHERE子句的主要功能有關，WHERE的主要功能是從全部的資料之中取出部分資料。

此時如果要對分組後的資料再次進行過濾，則使用HAVING子句完成，那麼此時的SQL文法格式如下：

SELECT [DISTINCT] *|分組欄位1 [別名] [,分組欄位2 [別名] ,…] | 統計函數FROM 表名稱 [別名], [表名稱 [別名] ,…][WHERE 條件(s)][GROUP BY 分組欄位1 [,分組欄位2 ,…]][HAVING 分組後的過濾條件（可以使用統計函數）][ORDER BY 排序欄位 ASC | DESC [,排序欄位 ASC | DESC]];

下面使用HAVING進行過濾。

SELECT d.deptno,d.dname,d.loc,COUNT(e.empno) mycount,NVL(AVG(e.sal),0) myavgFROM dept d,emp eWHERE d.deptno=e.deptno(+)GROUP BY d.deptno,d.dname,d.locHAVING AVG(sal)>2000;

注意點：WHERE和HAVING的區別

• WHERE：是在執行GROUP BY操作之前進行的過濾，表示從全部資料之中篩選出部分的資料，在WHERE之中不能使用統計函數；

• HAVING：是在GROUP BY分組之後的再次過濾，可以在HAVING子句中使用統計函數；

  思考題：顯示非銷售人員工作名稱以及從事同一工作僱員的月工資的總和，並且要滿足從事同一工作的僱員的月工資合計大於$5000，輸出結果按月工資的合計升序排列：

  第一步：查詢出所有的非銷售人員的資訊

  SELECT * FROM emp WHERE job<>‘SALESMAN‘;

  第二步：按照職位進行分組，並且使用SUM函數統計

  SELECT job,SUM(sal)FROM empWHERE job<>‘SALESMAN‘GROUP BY job;

  第三步：月工資的合計是通過統計函數查詢的，所以現在這個對分組後的過濾要使用HAVING子句完成

  SELECT job,SUM(sal)FROM empWHERE job<>‘SALESMAN‘GROUP BY jobHAVING SUM(sal)>5000;

  第四步：按照升序排列

  SELECT job,SUM(sal) sumFROM empWHERE job<>‘SALESMAN‘GROUP BY jobHAVING SUM(sal)>5000ORDER BY sum ASC;

  以上的題目就融合分組操作的大部分文法的使用，而且以後遇到問題，要慢慢分析。

  五、子查詢

子查詢 = 簡單查詢 + 限定查詢 + 多表查詢 + 統計查詢的綜合體；

在之前強調過多表查詢不建議大家使用，因為效能很差，但是多表查詢最有利的替代者就是子查詢，所以子查詢在實際的開發之中使用的相當的多；

所謂的子查詢指的就是在一個查詢之中嵌套了其他的若干查詢，嵌套子查詢之後的查詢SQL語句如下：

SELECT [DISTINCT] *|分組欄位1 [別名] [,分組欄位2 [別名] ,…] | 統計函數 ,(      SELECT [DISTINCT] *|分組欄位1 [別名] [,分組欄位2 [別名] ,…] | 統計函數      FROM 表名稱 [別名], [表名稱 [別名] ,…]      [WHERE 條件(s)]      [GROUP BY 分組欄位1 [,分組欄位2 ,…]]      [HAVING 分組後的過濾條件（可以使用統計函數）]      [ORDER BY 排序欄位 ASC | DESC [,排序欄位 ASC | DESC]])FROM 表名稱 [別名], [表名稱 [別名] ,…] ,(      SELECT [DISTINCT] *|分組欄位1 [別名] [,分組欄位2 [別名] ,…] | 統計函數      FROM 表名稱 [別名], [表名稱 [別名] ,…]      [WHERE 條件(s)]      [GROUP BY 分組欄位1 [,分組欄位2 ,…]]      [HAVING 分組後的過濾條件（可以使用統計函數）]      [ORDER BY 排序欄位 ASC | DESC [,排序欄位 ASC | DESC]])[WHERE 條件(s) (      SELECT [DISTINCT] *|分組欄位1 [別名] [,分組欄位2 [別名] ,…] | 統計函數      FROM 表名稱 [別名], [表名稱 [別名] ,…]      [WHERE 條件(s)]      [GROUP BY 分組欄位1 [,分組欄位2 ,…]]      [HAVING 分組後的過濾條件（可以使用統計函數）]      [ORDER BY 排序欄位 ASC | DESC [,排序欄位 ASC | DESC]])][GROUP BY 分組欄位1 [,分組欄位2 ,…]][HAVING 分組後的過濾條件（可以使用統計函數）][ORDER BY 排序欄位 ASC | DESC [,排序欄位 ASC | DESC]];

理論上子查詢可以出現在查詢語句的任意位置上，但是從個人而言，子查詢出現在WHERE和FROM子句之中較多；

以下的使用特點為個人總結，不是官方聲明的：

• WHERE：子查詢一般只返回單行列、多行單列、單行多列的資料；

• FROM：子查詢返回的一般是多行的資料，當作一張暫存資料表出現。

範例：要求查詢出工資比SMITH還要高的全部僱員資訊

要想完成本程式，首先必須要知道SMITH的工資是多少：

SELECT sal FROM emp WHERE ename=‘SMITH‘;

由於此時返回的是單列的資料，所以這個子句查詢可以在WHERE中出現。

SELECT * FROM empWHERE sal>(SELECT salFROM empWHERE ename=‘SMITH‘);

範例：要求查詢出高於公司平均工資的全部僱員資訊

公司的平均工資應該使用AVG()函數求出。

SELECT AVG(sal) FROM emp;

此時資料的返回結果是單行單列的資料，在WHERE之中出現。

SELECT * FROM empWHERE sal>(SELECT AVG(sal)FROM emp);

以上所返回的是單行單列，但是在子查詢之中，也可以返回單行多列的資料，只是這種子查詢很少出現。

範例：子查詢返回單行多列資料

SELECT * FROM empWHERE (job,sal)=(SELECT job,salFROM empWHERE ename=‘ALLEN‘);

如果現在的子查詢返回的是多行單列資料的話，這個時候就需要使用三種判斷符判斷了：IN、ANY、ALL；

1、 IN操作符：用於指定一個子查詢的判斷範圍

這個操作符的使用實際上與之前講解的IN是一樣的，唯一不同的是，裡面的範圍由子查詢指定了。

SELECT * FROM empWHERE sal in (SELECT salFROM empWHERE job=‘MANAGER‘);

但是在使用IN的時候還要注意NOT IN的問題，如果使用NOT IN操作，在子查詢之中，如果有一個內容是null，則不會查詢出任何的結果。

2、 ANY操作符：與每一個內容想匹配，有三種匹配形式

• =ANY：功能與IN操作符是完全一樣的；

SELECT * FROM empWHERE sal=ANY (SELECT salFROM empWHERE job=‘MANAGER‘);

• >ANY：比子查詢中返回記錄最小的還要大的資料；

SELECT * FROM empWHERE sal>ANY (SELECT salFROM empWHERE job=‘MANAGER‘);

• <ANY：比子查詢中返回記錄的最大的還要小；

SELECT * FROM empWHERE sal<ANY (SELECT salFROM empWHERE job=‘MANAGER‘);

3、 ALL操作符：與每一個內容相匹配，有兩種匹配形式：

• >ALL：比子查詢中返回的最大的記錄還要大

SELECT * FROM empWHERE sal>ALL (SELECT salFROM empWHERE job=‘MANAGER‘);

• <ALL：比子查詢中返回的最小的記錄還要小

SELECT * FROM empWHERE sal<ALL (SELECT salFROM empWHERE job=‘MANAGER‘);

以上的所有子查詢都是在WHERE子句中出現的，那麼下面再來觀察在FROM子句中出現的查詢，這個子查詢一般返回的是多行多列的資料，當作一張暫存資料表的方式來處理。

範例：查詢出每個部門的編號、名稱、位置、部門人數、平均工資

• 回顧：最早的時候使用的是多欄位分組統計完成的：

SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal)FROM emp e,dept dWHERE e.deptno(+)=d.deptnoGROUP BY d.deptno,d.dname,d.loc;

這個時候實際上是產生了笛卡爾積，一共產生了56條記錄；

• 新的解決方案：通過子查詢完成，所有的統計查詢只能在GROUP BY中出現，所以在子查詢之中負責統計資料，而在外部的查詢之中，負責將統計資料和dept表資料相統一。

SELECT d.deptno,d.dname,d.loc,temp.count,temp.avgFROM dept d,(SELECT deptno dno,COUNT(empno) count,AVG(sal) avgFROM empGROUP BY deptno) tempWHERE d.deptno=temp.dno(+);

現在的程式中所操作的資料量：

• 子查詢中統計的記錄是14條記錄，最終統計的顯示結果是3條記錄；

• dept表之中一共有4條記錄；

• 如果現在產生笛卡爾積的話只有12條記錄，再加上僱員的14條記錄，一共才26條記錄；

通過如上的分析，可以發現，使用子查詢的確要比使用多表查詢更加節省效能，所以在開發之中子查詢出現是最多的，而且在給出一個不成文的規定：大部分情況下，如果最終的查詢結果之中需要出現SELECT子句，但是又不能直接使用統計函數的時候，就在子查詢中統計資訊，即：有複雜統計的地方大部分都需要子查詢。

 

Oracle 多表查詢(2)