oracle 多表聯集查詢,統計查詢,組函數,order by,having,子查詢,集合運算

多表聯集查詢 
通過串連可以建立多表查詢，多表查詢的資料可以來自多個表，但是表之間必須有適當的串連條件。為了從多張表中查詢，必須識別串連多張表的公用列。一般是在WHERE子句中用比較子指明串連的條件。 

忘記說明表的串連條件是常見的一種錯誤，這時查詢將會產生表串連的笛卡爾積(即一個表中的每條記錄與另一個表中的每條記錄作串連產生的結果)。一般N個表進行串連，需要至少N-1個串連條件，才能夠正確串連。兩個表串連是最常見的情況，只需要說明一個串連條件。 

兩個表的串連有四種串連方式： 
* 相等串連。 
* 不等串連。 
* 外串連。 
* 自串連。 
1．相等串連 
通過兩個表具有相同意義的列，可以建立相等串連條件。使用相等串連進行兩個表的查詢時，只有串連列上在兩個表中都出現且值相等的行才會出現在查詢結果中。 

顯示僱員的名稱和所在的部門的編號和名稱。 
執行以下查詢： 

Sql代碼  

1. 
SELECT emp.ename,emp.deptno,dept.dname FROM emp,dept   

1.         WHERE emp.deptno=dept.deptno;  

執行結果如下： 

Sql代碼  

1. 
ENAME          DEPTNO DNAME  

1.         ------------- ------------------------ - ----------  
2.         SMITH              20     RESEARCH  
3.         ALLEN              30     SALES   

說明：相等串連語句的格式要求是，在FROM從句中依次列出兩個表的名稱，在表的每個列前需要添加表名，用“.”分隔，表示列屬於不同的表。在WHERE條件中要指明進行相等串連的列。 
以上訓練中，不在兩個表中同時出現的列，前面的表名首碼可以省略。所以以上例子可以簡化為如下的表示： 
SELECT ename,emp.deptno,dname FROM emp,dept 
WHERE emp.deptno=dept.deptno; 

2．外串連 
在以上的例子中，相等串連有一個問題：如果某個僱員的部門還沒有填寫，即保留為空白，那麼該僱員在查詢中就不會出現；或者某個部門還沒有僱員，該部門在查詢中也不會出現。 
為瞭解決這個問題可以用外連，即除了顯示滿足相等串連條件的記錄外，還顯示那些不滿足串連條件的行，不滿足串連條件的行將顯示在最後。外連操作符為(+)，它可以出現在相等串連條件的左側或右側。出現在左側或右側的含義不同，這裡用如下的例子予以說明。 

使用外連顯示不滿足相等條件的記錄。 
步驟1：顯示僱員的名稱、工資和所在的部門名稱及沒有任何僱員的部門。 
執行以下查詢： 

Sql代碼  

1. 
SELECT ename,sal,dname FROM emp,dept   

1.         WHERE emp.deptno(+)=dept.deptno;  

執行結果為： 

Sql代碼  

1. 
ENAME            SAL DNAME  

1.         ------------------- -------------- ------------------------  

3. 
CLARK               2450 ACCOUNTING  

1.         KING                5000 ACCOUNTING  
2.         MILLER              1300 ACCOUNTING  
3.         ...  
4.         TURNER              1500 SALES  
5.         WARD                1250 SALES  
6.                         OPERATIONS  

3．不等串連 
還可以進行不等的串連。以下是一個訓練執行個體，其中用到的salgrade表的結構如下： 
DESC salgrade 

Sql代碼  

1. 
名稱                              是否為空白  類型  

1.         ------------------------------------------- ------------------ ------------------  
2.         GRADE                                 NUMBER  
3.         LOSAL                                     NUMBER  
4.          HISAL                                        NUMBER  

Grade 表示工資等級，losal和hisal分別表示某等級工資的下限和上限。 
表的內容為： 

Sql代碼  

1. 
SELECT * FROM salgrade;   

 

Sql代碼  

1. 
GRADE      LOSAL    HISAL  

1.         ------------------- ------------------- -------------  
2.                  1        700           1200  
3.                  2       1201           1400  
4.                  3       1401           2000  
5.                 4       2001        3000  
6.                 5       3001        9999  

顯示僱員名稱，工資和所屬工資等級。 
執行以下查詢： 

Sql代碼  

1. 
SELECT e.ename, e.sal, s.grade FROM emp e,salgrade s  

1.         WHERE   e.sal BETWEEN s.losal AND s.hisal;  

執行結果為： 

Sql代碼  

1. 
ENAME             SAL      GRADE  

1.         ----------------- -------------------- -------------------  

3. 
JONES               2975          4  

1.         BLAKE               2850          4  
2.         CLARK               2450          4  
3.         SCOTT               3000          4  
4.         FORD                             3000          4  
5.         KING                     5000          5  

說明：通過將僱員工資與不同的工資上下限範圍相比較，取得工資的等級，並在查詢結果中顯示出僱員的工資等級。 

4．自串連 
最後是一個自串連的訓練執行個體，自串連就是一個表，同本身進行串連。對於自串連可以想像存在兩個相同的表(表和表的副本)，可以通過不同的別名區別兩個相同的表。 
顯示僱員名稱和僱員的經理名稱。 
執行以下查詢： 

Sql代碼  

1. 
SELECT worker.ename||' 的經理是 '||manager.ename AS 僱員經理   

1.         FROM emp worker, emp manager  
2.         WHERE worker.mgr = manager.empno;  

執行結果為： 

Sql代碼  

1. 
僱員經理  

2. 
-------------------------------------------  

1.         SMITH 的經理是 FORD  
2.         ALLEN 的經理是 BLAKE  
3.         WARD 的經理是 BLAKE  

說明：為EMP表分別起了兩個別名worker和manager，可以想像，第一個表是僱員表，第二個表是經理表，因為經理也是僱員。然後通過worker表的mgr(經理編號)欄位同manager表的empno(僱員編號)欄位建立串連，這樣就可以顯示僱員的經理名稱了。 
注意：經理編號mgr是僱員編號empno之一，所以經理編號可以同僱員編號建立串連。 

統計查詢 
  通常需要對資料進行統計，匯總出資料庫的統計資訊。比如，我們可能想瞭解公司的總人數和總工資額，或各個部門的人數和工資額，這個功能可以由統計查詢完成。 
Oracle提供了一些函數來完成統計工作，這些函數稱為組函數，組函數不同於前面介紹和使用的函數(單行函數)。組函數可以對分組的資料進行求和、求平均值等運算。組函數只能應用於SELECT子句、HAVING子句或ORDER BY子句中。組函數也可以稱為統計函數。 

組函數: 
AVG:求平均值 
COUNT:求計數值，返回非空行數，*表示返回所有行 
MAX:求最大值 
MIN:求最小值 
SUM:求和 
STDDEV:求標準差，是根據差的平方根得到的 
VARIANCE:求統計方差 

分組函數中SUM和AVG只應用於數值型的列，MAX、MIN和COUNT可以應用於字元、數值和日期類型的列。組函數忽略列的空值。 
使用GROUP BY 從句可以對資料進行分組。所謂分組，就是按照列的相同內容，將記錄劃分成組，對組可以應用組函數。 
如果不使用分組，將對整個表或滿足條件的記錄應用組函數。 
在組函數中可使用DISTINCT或ALL關鍵字。ALL表示對所有非NULL值(可重複)進行運算(COUNT除外)。DISTINCT 表示對每一個非NULL值，如果存在重複值，則組函數只運算一次。如果不指明上述關鍵字，預設為ALL。 

求有傭金的僱員人數。 
執行以下查詢： 

Sql代碼  

1. 
SELECT COUNT(comm) FROM emp;  

返回結果為： 

Sql代碼  

1. 
COUNT(COMM)  

1.         ---------------------  
2.                  4  

說明：在本例中，沒有返回全部僱員，只返回傭金非空的僱員，只有4個人。 

求僱員表中不同職務的個數。 
執行以下查詢： 

Sql代碼  

1. 
SELECT COUNT( DISTINCT job) FROM emp;  

返回結果為： 

Sql代碼  

1. 
COUNT(DISTINCT JOB)  

1.         -------------------------------  
2.                        5  

說明：該查詢返回僱員表中不同職務的個數。如果不加DISTINCT，則返回的是職務非空的僱員個數。 

分組統計 
通過下面的訓練，我們來瞭解分組的用法。 
按職務統計工資總和。 
步驟1：執行以下查詢： 

Sql代碼  

1. 
SELECT job,SUM(sal) FROM emp GROUP BY job;  

執行結果為： 

Sql代碼  

1.   JOB         SUM(SAL)  

2. 
----------------- -------------------  

3. 
ANALYST         6000  

4. 
CLERK               4150  

5. 
MANAGER         8275  

6. 
PRESIDENT           5000  

7. 
SALESMAN        5600  

說明：分組查詢允許在查詢列表中包含分組列，對以上執行個體，因為是按職務job分組的，所以在查詢列中可以包含job欄位，使統計結果很清楚 
職務為ANALYST的僱員的總工資為6000，職務為CLERK的僱員的總工資為4150，依此類推。 
注意：在查詢列中，不能使用分組列以外的其他列，否則會產生錯誤資訊。 

錯誤寫法:SELECT ename,job,SUM(sal) FROM emp GROUP BY job; 

多列分組統計 
可以按多列進行分組，以下是按兩列進行分組的例子。 
按部門和職務分組統計工資總和。 
執行以下查詢： 

Sql代碼  

1. 
SELECT   deptno, job, sum(sal) FROM emp   

2. 
GROUP BY deptno, job;  

執行結果為： 
    DEPTNO JOB        
SUM(SAL) 
------------------ --------- ----------------------- 
10 CLERK         1300 
        10
MANAGER      2450 
        10 PRESIDENT    
5000 
        20
ANALYST      6000 
        20
CLERK         1900 
        20
MANAGER       2975 
        30
CLERK          950 
        30
MANAGER       2850 
        30
SALESMAN      5600 
說明：該查詢統計每個部門中每種職務的總工資。 
分組統計結果限定 
對分組查詢的結果進行過濾，要使用HAVING從句。HAVING從句過濾分組後的結果，它只能出現在GROUP BY從句之後，而WHERE從句要出現在GROUP BY從句之前。 
統計各部門的最高工資，排除最高工資小於3000的部門。 
執行以下查詢： 

Sql代碼  

1. 
SELECT   deptno, max(sal) FROM emp  

1.         GROUP BY deptno  
2.         HAVING   max(sal)>=3000;  

執行結果為： 

Sql代碼  

1. 
DEPTNO   MAX(SAL)  

1.         ------------------ ------------------  
2.                 10       5000  
3.              20       3000  

說明：結果中排除了部門30，因部門30的總工資小於3000。 
注意：HAVING從句的限定條件中要出現組函數。如果同時使用WHERE條件，則WHERE條件在分組之前執行，HAVING條件在分組後執行。 

分組統計結果排序 
   可以使用ORDER BY從句對統計的結果進行排序，ORDER BY從句要出現在語句的最後。 
按職務統計工資總和並排序。 
執行以下查詢： 

Sql代碼  

1. 
SELECT job 職務, SUM(sal) 工資總和 FROM emp  

2. 
GROUP BY job  

3. 
ORDER BY SUM(sal);  

執行結果為： 

Sql代碼  

1. 
職務           工資總和  

2. 
---------------- ------------------  

3. 
CLERK           4150  

4. 
PRESIDENT           5000  

5. 
SALESMAN        5600  

6. 
ANALYST         6000  

7. 
MANAGER         8275  

組函數的嵌套使用 
在如下訓練中，使用了組函數的嵌套。 
求各部門平均工資的最高值。 
執行以下查詢： 

Sql代碼  

1. 
SELECT max(avg(sal)) FROM emp GROUP BY deptno;  

執行結果為： 

Sql代碼  

1. 
MAX(AVG(SAL))  

1.         -----------------------  
2.         2916.66667  

說明：該查詢先統計各部門的平均工資，然後求得其中的最大值。 
注意：雖然在查詢中有分組列，但在查詢欄位中不能出現分組列。 

子查詢 
   我們可能會提出這樣的問題，在僱員中誰的工資最高，或者誰的工資比SCOTT高。通過把一個查詢的結果作為另一個查詢的一部分，可以實現這樣的查詢功能。具體的講：要查詢工資高於SCOTT的僱員的名字和工資，必須通過兩個步驟來完成，第一步查詢僱員SCOTT的工資，第二步查詢工資高於SCOTT的僱員。第一個查詢可以作為第二個查詢的一部分出現在第二個查詢的條件中，這就是子查詢。出現在其他查詢中的查詢稱為子查詢，包含其他查詢的查詢稱為主查詢。 

子查詢一般出現在SELECT語句的WHERE子句中，Oracle也支援在FROM或HAVING子句中出現子查詢。子查詢比主查詢先執行，結果作為主查詢的條件，在書寫上要用圓括弧擴起來，並放在比較子的右側。子查詢可以嵌套使用，最裡層的查詢最先執行。子查詢可以在SELECT、INSERT、UPDATE、DELETE等語句中使用。 
子查詢按照返回資料的類型可以分為單行子查詢、多行子查詢和多列子查詢。 

單行子查詢 
查詢比SCOTT工資高的僱員名字和工資。 
執行以下查詢： 

Sql代碼  

1. 
SELECT ename,sal FROM emp  

2. 
WHERE sal>(SELECT sal FROM emp WHERE empno=7788);  

執行結果為： 

Sql代碼  

1. 
ENAME             SAL  

2. 
-------------- --------------------  

3. 
KING              5000  

說明：在該子查詢中查詢SCOTT的工資時使用的是他的僱員號，這是因為僱員號在表中是惟一的，而僱員的姓名有可能相重。SCOTT的僱員號為7788。 
也可以包含兩個或多個子查詢。 

Sql代碼  

1. 
在FROM從句中使用子查詢  

在FROM從句中也可以使用子查詢，在原理上這與在WHERE條件中使用子查詢類似。有的時候我們可能要求從僱員表中按照僱員出現的位置來檢索僱員，很容易想到的是使用rownum虛列。比如我們要求顯示僱員表中6～9位置上的僱員，可以用以下方法。 

查詢僱員表中排在第6～9位置上的僱員。 
執行以下查詢： 

Sql代碼  

1. 
SELECT ename,sal FROM (SELECT rownum as num,ename,sal FROM emp WHERE rownum<=9 )  

1.         WHERE num>=6;  

執行結果為： 

Sql代碼  

1. 
ENAME               SAL  

1.         -------------- --------------------  
2.         BLAKE               2850  
3.         CLARK               2450  
4.         SCOTT               3000  
5.         KING                5000  

說明：子查詢出現在FROM從句中，檢索出行號小於等於9的僱員，並產生num編號列。在主查詢中檢索行號大於等於6的僱員。 
   注意：以下用法不會有查詢結果 
SELECT ename,sal FROM emp 
WHERE rownum>=6 AND rownum<=9; 

集合運算 
多個查詢語句的結果可以做集合運算，結果集的欄位類型、數量和順序應該一樣。 
Oracle共有4個集合操作 

UNION:並集，合并兩個操作的結果，去掉重複的部分 
UNION ALL:並集，合并兩個操作的結果，保留重複的部分 
MINUS:差集，從前面的操作結果中去掉與後面操作結果相同的部分 
INTERSECT:交集，取兩個操作結果中相同的部分 

使用集合的並運算 
查詢部門10和部門20的所有職務。 
執行以下查詢： 

Sql代碼  

1. 
SELECT  job FROM emp WHERE deptno=10  

1.         UNION  
2.         SELECT  job FROM emp WHERE deptno=20;  

執行結果為： 

Sql代碼  

1. 
JOB  

2. 
---------  

3. 
ANALYST  

4. 
CLERK  

5. 
MANAGER  

6. 
PRESIDENT  

說明：部門10的職務有PRESIDENT、MANAGER、CLERK；部門20的職務有MANAGER、CLERK、ANALYST。所以兩個部門的所有職務(相同職務只算一個)共有4個：ANALYST、CLERK、MANAGER和PRESIDENT。 
將UNION改為UNION ALL的結果為:把兩條語句查出來的值全部都顯示,不去掉重複值 

使用集合的交運算 
查詢部門10和20中是否有相同的職務和工資。 
執行以下查詢： 

Sql代碼  

1. 
SELECT  job,sal FROM emp WHERE deptno=10  

1.         INTERSECT  
2.         SELECT  job,sal FROM emp WHERE deptno=20;  

執行結果為： 
未選定行 

說明：部門10的職務有PRESIDENT、MANAGER、CLERK；部門20的職務有MANAGER、CLERK、ANALYST。所以兩個部門的相同職務為：CLERK和MANAGER。但是職務和工資都相同的僱員沒有，所以沒有結果。 
使用集合的差運算 
查詢只在部門表中出現，但沒有在僱員表中出現的部門編號。 
執行以下查詢： 

Sql代碼  

1. 
SELECT  deptno FROM dept  

2. 
MINUS  

3. 
SELECT  deptno FROM emp ;  

執行結果為： 

Sql代碼  

1. 
DEPTNO  

2. 
------------------  

1.              40  

說明：部門表中的部門編號有10、20、30和40。僱員表中的部門編號有10、20和30。差集的結果為40。