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

一、多表聯集查詢
通過串連可以建立多表查詢，多表查詢的資料可以來自多個表，但是表之間必須有適當的串連條件。為了從多張表中查詢，必須識別串連多張表的公用列。一般是在WHERE子句中用比較子指明串連的條件。
                
兩個表串連有四種串連方式：
* 相等串連
* 不等串連(看作單表查詢)
* 外串連
* 自串連(自關聯)
        
1．相等串連
通過兩個表具有相同意義的列，可以建立相等串連條件。使用相等串連進行兩個表的查詢時，只有串連列上在兩個表中都出現且值相等的行才會出現在查詢結果中    
顯示僱員名稱和所在部門的編號和名稱。
執行以下查詢：
SELECT a.ename, b.id, b.dname
FROM employee a,dept b  
WHERE a.id=b.id
說明：相等串連語句的格式要求是，在FROM從句中依次列出兩個表的名稱，在表的每個列前需要添加表名，用“.”分隔，表示列屬於不同的表。在WHERE條件中要指明進行相等串連的列。
以上訓練中，不在兩個表中同時出現的列，前面的表名首碼可以省略。所以以上例子可以簡化為如下的表示：
SELECT ename, b.id, dname
FROM employee a,dept b  
WHERE a.id=b.id
   
2．外串連
在以上的例子中，相等串連有一個問題：如果某個僱員的部門還沒有填寫，即保留為空白，那麼該僱員在查詢中就不會出現；或者某個部門還沒有僱員，該部門在查詢中也不會出現。
為瞭解決這個問題可以用外連，即除了顯示滿足相等串連條件的記錄外，還顯示那些不滿足串連條件的行，不滿足串連條件的行將顯示在最後。外連操作符為(+)，它可以出現在相等串連條件的左側或右側。出現在左側或右側的含義不同，這裡用如下的例子予以說明。
     
使用外連顯示不滿足相等條件的記錄。
顯示僱員名稱和所在部門的編號和名稱。
執行以下查詢：
左串連方法一(推薦使用，簡潔)：
SELECT ename, b.id, dname
FROM employee a,dept b  
WHERE a.id(+)=b.id

左串連方法二：
SELECT ename, b.id, dname
FROM dept b
LEFT JOIN employee a ON a.id=b.id
注意：不管dept是否存在，employee都會顯示
   
3、自串連(一般用在樹形許可權結構中)
自串連就是一個表，同本身進行串連。對於自串連可以想像存在兩個相同的表(表和表的副本)，可以通過不同的別名區別兩個相同的表。
SELECT worker.ename||' 的經理是 '||manager.ename AS 僱員經理  
FROM employee worker, employee manager 
WHERE worker.mgr = manager.empno;
       
------------
執行結果為：
1.SMITH 的經理是 FORD 
2.ALLEN 的經理是 BLAKE 
3.WARD 的經理是 BLAKE 

  
註：在操作多表聯集查詢時，若出現以下情況，將形成笛卡爾積
– 聯結條件被省略
– 聯結條件無效
– 第一個表中的所有行被聯結到第二個表中的所有行上
為了避免笛卡爾積，請始終包括有效聯結條件
    
何為笛卡爾積？
笛卡爾（Descartes）乘積又叫直積。假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以擴充到多個集合的情況。
        
二、統計查詢
通常需要對資料進行統計，匯總出資料庫的統計資訊。
比如，我們可能想瞭解公司的總人數和總工資額，或各個部門的人數和工資額，這個功能可以由統計查詢完成。
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。
        
求僱員表中不同職務的個數
SELECT COUNT(DISTINCT job) FROM employee
按職務統計工資總和
SELECT job,SUM(sal) FROM employee GROUP BY job
按部門和職務分組統計工資總和
SELECT deptno, job, sum(sal) FROM employee  
GROUP BY deptno, job; 
統計各部門的最高工資，排除最高工資小於3000的部門。
SELECT deptno, max(sal) FROM employee 
GROUP BY deptno HAVING max(sal)>=3000; 
按職務統計工資總和並排序
SELECT job 職務, SUM(sal) 工資總和
FROM employee 
GROUP BY job 
ORDER BY SUM(sal); 
求各部門平均工資的最高值
SELECT max(avg(sal)) FROM employee GROUP BY deptno
      
子查詢
我們可能會提出這樣的問題，在僱員中誰的工資最高，或者誰的工資比SCOTT高。通過把一個查詢的結果作為另一個查詢的一部分，可以實現這樣的查詢功能。
具體的講：要查詢工資高於SCOTT的僱員的名字和工資，必須通過兩個步驟來完成，
第一步查詢僱員SCOTT的工資，
第二步查詢工資高於SCOTT的僱員。
第一個查詢可以作為第二個查詢的一部分出現在第二個查詢的條件中，這就是子查詢。出現在其他查詢中的查詢稱為子查詢，包含其他查詢的查詢稱為主查詢。
      
子查詢一般出現在SELECT語句的WHERE子句中，Oracle也支援在FROM或HAVING子句中出現子查詢。子查詢比主查詢先執行，結果作為主查詢的條件，在書寫上要用圓括弧擴起來，並放在比較子的右側。子查詢可以嵌套使用，最裡層的查詢最先執行。子查詢可以在SELECT、INSERT、UPDATE、DELETE等語句中使用。
子查詢按照返回資料的類型可以分為單行子查詢、多行子查詢和多列子查詢。
  
查詢比SCOTT工資高的僱員名字和工資
SELECT ename, sal FROM employee 
WHERE sal>(SELECT sal FROM employee WHERE empno=7788); 
查詢僱員表中排在第6～9位置上的僱員
SELECT ename,sal FROM (SELECT rownum as num,ename,sal FROM employee WHERE rownum<=9 ) 
WHERE num>=6;
說明：子查詢出現在FROM從句中，檢索出行號小於等於9的僱員，並產生num編號列。在主查詢中檢索行號大於等於6的僱員。
注意：以下用法不會有查詢結果
SELECT ename,sal FROM employee
WHERE rownum>=6 AND rownum<=9
           
集合運算
多個查詢語句的結果可以做集合運算，結果集的欄位類型、數量和順序應該一樣。
Oracle共有4個集合操作
    
UNION:並集，合并兩個操作的結果，去掉重複的部分
UNION ALL:並集，合并兩個操作的結果，保留重複的部分
MINUS:差集，從前面的操作結果中去掉與後面操作結果相同的部分
INTERSECT:交集，取兩個操作結果中相同的部分
   
查詢部門10和部門20的所有職務。
SELECT  job FROM employee WHERE deptno=10 
UNION 
SELECT  job FROM emp WHERE deptno=20; 
查詢部門10和20中是否有相同的職務和工資。
SELECT  job,sal FROM employee WHERE deptno=10 
INTERSECT 
SELECT  job,sal FROM employee WHERE deptno=20
查詢只在部門表中出現，但沒有在僱員表中出現的部門編號
SELECT  deptno FROM dept 
MINUS 
SELECT  deptno FROM employee