SQL語言基礎(高階查詢)

1-1 集合查詢

運算子	返回結果集	內容
UNION	由每個查詢選擇的所有不重複的行	並集。不包含重複值，預設按第 1 個查詢的第 1 列升序排列
UNION ALL	由每個查詢選擇的所有的行，包括所有重複的行	包括所有重複的行，完全並集包含重複值，不排序
INTERSECT	由每個查詢選擇的所有不重複的相交行	交集。不包含重複行，按第 1 個查詢的第 1 列升序排列
MINUS	在第一個查詢中，不在後面查詢中的行	不包含重複行，按第 1 個查詢的第 1 列升序排列

除 UNION ALL 之外，系統會自動將重複的記錄刪除

系統將第一個查詢的列名顯示在輸出中

除 UNION ALL 之外，系統自動按照第一個查詢中的第一個列的升序排列

1-1-1  UNION 運算子

SELECT employee_id, job_id
FROM   employees
UNION
SELECT employee_id, job_id
FROM   job_history;

1-1-2  UNION ALL 運算子

UNION ALL 運算子返回兩個查詢的結果集的並集以及兩個結果集的重複部分（不去重複值）

SELECT employee_id, job_id, department_id
FROM   employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM   job_history

1-1-3  INTERSECT 運算子

INTERSECT 運算子返回兩個結果集的交集

SELECT employee_id, job_id
FROM   employees
INTERSECT
SELECT employee_id, job_id
FROM   job_history;

1-1-4  MINUS 運算子

SELECT employee_id,job_id
FROM   employees
MINUS
SELECT employee_id,job_id
FROM   job_history;

使用集合的注意事項：

在SELECT 列表中的列名和表示式在數量和資料型別上要相對應

括號可以改變執行的順序

ORDER BY 子句:

只能在語句的最後出現

可以使用第一個查詢中的列名, 別名或相對位置

1-2 分級查詢

SELECT [LEVEL], column, expr...
FROM   table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)] ;

1. 其中level 關鍵字是可選的，表示等級。

2.From 之後可以是table,view 但是隻能是一個table 。

3.Where 條件限制了查詢返回的行，但是不影響層次關係，不滿足條件的節點不返回，但是這個不滿足條件的節點的下層child 不受影響。

4.Start with 是表示開始節點，對於一個真實的層次關係，必須要有這個子句，但是不是必須的，後面詳細介紹。

5.connect by prior 是指定父子關係，其中prior 的位置不一定要在connect by 之後，對於一個真實的層次關係，這也是必須的。

1-2-1 樹形結構

 樹結構的資料存放在表中，資料之間的層次關係即父子關係，透過表中的列與列間的關係來描述，如該表中的EMPLOYEE_ID 和MANAGER_ID 。 EMPLOYEE_ID 表示該僱員的編號， MANAGER_ID 表示領導該僱員的人的編號，即子節點的MANAGER_ID 值等於父節點的EMPLOYEE_ID 值。在表的每一行中都有一個表示父節點的MANAGER_ID （除根節點外），透過每個節點的父節點，就可以確定整個樹結構。

1-2-2 遍歷樹

   首先必須確定起始點，透過start with 子句，後面加條件，這個條件是任何合法的條件表示式。

　　  Start with 確定將哪行作為root ，如果沒有start with, 則每行都當作root ，然後查詢其後代，這不是一個真實的查詢。Start with 後面可以使用子查詢，如果有where 條件，則會截斷層次中的相關滿足條件的節點，但是不影響整個層次結構。可以帶多個條件。

   運算子PRIOR 被放置於等號前後的位置，決定著查詢時的檢索順序。

        PRIOR 被置於CONNECT BY 子句中等號的前面時，則強制從根節點到葉節點的順序檢索，即由父節點向子節點方向透過樹結構，我們稱之為自頂向下的方式。

        PIROR 運算子被置於CONNECT BY 子句中等號的後面時，則強制從葉節點到根節點的順序檢索，即由子節點向父節點方向透過樹結構，我們稱之為自底向上的方式。

CONNECT BY PRIOR column1 = column2

遍歷樹: 從底到頂

從底到頂查詢樹結構時 , 也要指定一個開始節點，以此開始向上查詢其父節點，直至找到根節點，其結果將是結構樹中的一枝資料

SELECT employee_id, last_name, job_id, manager_id
FROM   employees
START  WITH  employee_id = 101
CONNECT BY PRIOR manager_id = employee_id ;

遍歷樹: 從頂到底

  在自頂向下查詢樹結構時，不但可以從根節點開始，還可以定義任何節點為起始節點，以此開始向下查詢。這樣查詢的結果就是以該節點為開始的結構樹的一枝。

SELECT  last_name||' reports to '||
PRIOR   last_name "Walk Top Down"
FROM    employees
START   WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id ;

1-2-3  使用 LEVEL 偽列標記層次

在查詢中，可以使用偽列LEVEL 顯示每行資料的有關層次。LEVEL 將返回樹型結構中當前節點的層次，可以使用LEVEL 來控制對樹型結構進行遍歷的深度。

在具有樹結構的表中，每一行資料都是樹結構中的一個節點，由於節點所處的層次位置不同，所以每行記錄都可以有一個層號。層號根據節點與根節點的距離確定。不論從哪個節點開始，該起始根節點的層號始終為1 ，根節點的子節點為2 ， 依此類推 。

使用 LEVEL 和 LPAD 格式化分層查詢

COLUMN org_chart FORMAT A12
SELECT level,
              LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,‘-')
       AS org_chart
FROM   employees
START WITH last_name='King'
CONNECT BY PRIOR employee_id=manager_id

LEVEL  ORG_CHART

---                   -----------

1  King

2  - Kochhar

3  --Whalen

3  --Higgins

4  --- Gietz

2  -De Haan

3  -- Hunold

4  --- Emst

4  ---Lorentz

2  - Mourgos

3  -- Rajs

3  --Davies

3  --Matos

3  --Vargas

2  - Zlotkey

3  --Abel

3  --Taylor

3  --Gant

2  - Hartstein

3  --Fay

1-2-4 修剪分支

     where 子句會將節點刪除，但是其後代不會受到影響

    connect by 中加上條件會將滿足條件的整個樹枝包括後代都刪除

1-3  GROUP BY 子句的擴充套件

使用 ROLLUP 操作分組

使用 CUBE 操作分組

使用 GROUPING 函式處理 ROLLUP 或 CUBE 操作所產生的空值

使用 GROUPING SETS 操作進行單獨分組

1-3-1  帶有 ROLLUP 和 CUBE 操作的 GROUP BY 子句

使用帶有ROLLUP 和 CUBE 操作的GROUP BY 子句產生多種分組結果

ROLLUP 產生n + 1 種分組結果

CUBE 產生2 的n 次方種分組結果

ROLLUP 運算子

SELECT  [column,] group_function(column). . .
FROM  table
[WHERE  condition]
[GROUP BY  [ROLLUP] group_by_expression]
[HAVING   having_expression];
[ORDER BY  column];

ROLLUP 是對 GROUP BY 子句的擴充套件

ROLLUP 產生n + 1 種分組結果，順序是從右向左

SELECT   department_id, job_id, SUM(salary)
FROM     employees 
WHERE    department_id < 60
GROUP BY ROLLUP(department_id, job_id);

CUBE 運算子

SELECT  [column,] group_function(column)...
FROM  table
[WHERE  condition]
[GROUP BY  [CUBE] group_by_expression]
[HAVING   having_expression]
[ORDER BY  column];

CUBE 是對 GROUP BY 子句的擴充套件

CUBE 會產生類似於笛卡爾集的分組結果

SELECT   department_id, job_id, SUM(salary)
FROM     employees 
WHERE    department_id < 60
GROUP BY CUBE (department_id, job_id) ;

1-3-2  GROUPING 函式

SELECT    [column,] group_function(column) . ,
          GROUPING(expr)
FROM       table
[WHERE    condition]
[GROUP BY [ROLLUP][CUBE] group_by_expression]
[HAVING   having_expression]
[ORDER BY column];

GROUPING 函式可以和 CUBE 或 ROLLUP 結合使用

使用 GROUPING 函式，可以找到哪些列在該行中參加了分組

使用 GROUPING 函式, 可以區分空值產生的原因

GROUPING 函式返回 0 或 1

SELECT   department_id DEPTID, job_id JOB,
         SUM(salary),
         GROUPING(department_id) GRP_DEPT,
         GROUPING(job_id) GRP_JOB
FROM     employees
WHERE    department_id < 50
GROUP BY ROLLUP(department_id, job_id);

1-3-3  GROUPING SETS

GROUPING SETS 是對GROUP BY 子句的進一步擴充

使用 GROUPING SETS 在同一個查詢中定義多個分組集

Oracle 對 GROUPING SETS 子句指定的分組集進行分組後用 UNION ALL 操作將各分組結果結合起來

Grouping set 的優點:

只進行一次分組即可

不必書寫複雜的 UNION 語句

SELECT   department_id, job_id,
         manager_id,avg(salary)
FROM     employees
GROUP BY GROUPING SETS
((department_id,job_id), (job_id,manager_id));

1-3-4  複合列

複合列是被作為整體處理的一組列的集合

ROLLUP (a,b,c,d)

使用括號將若干列組成複合列在ROLLUP 或 CUBE 中作為整體進行操作

在ROLLUP 或 CUBE 中, 複合列可以避免產生不必要的分組結果

SELECT   department_id, job_id, manager_id,
         SUM(salary)
FROM     employees 
GROUP BY ROLLUP( department_id,(job_id, manager_id));

1-3-5  連線分組集

連線分組集可以產生有用的對分組項的結合

將各分組集, ROLLUP 和 CUBE 用逗號連線 Oracle 自動在 GROUP BY 子句中將各分組集進行連線

連線的結果是對各分組生成笛卡爾集

SELECT   department_id, job_id, manager_id,
         SUM(salary)
FROM     employees
GROUP BY department_id,
         ROLLUP(job_id),
         CUBE(manager_id);