Oracle [CONNECT BY]

之前有介紹 (SQL Server) CTE RECURSIVE (遞迴)製作月曆 在Oracle 有提供一個精簡的語法產生階層資料 CONNECT BY

 CONNECT BY 有幾個常用Key Word.

1. LEVEL 目前在樹節點第幾階層
2. START WITH 設定哪筆做為起始點開始樹
3. PRIOR 用於指定父資料欄位

製造出的階層樹，概念如下

[圖來自Oracle]

範例一

建立連續數字 1~10

<code class="language-sql">SELECT X + LEVEL
FROM (
  SELECT 0 X
  FROM DUAL
 )
CONNECT BY LEVEL <= 10

此範例使用 LEVEL 在 CONNECT BY 上當條件 建立列值到LEVEL 大於等於 10

sqlfiddle

範例二

建立日曆表

<code class="language-sql">SELECT startDt + LEVEL - 1
FROM (
  SELECT sysdate endDt,  (sysdate -10) startDt
  FROM DUAL
)t1
CONNECT BY startDt - endDt + LEVEL <= 0

一開始有兩個欄位 

1. StartDt 起始時間(10天前)
2. EndDt  最後時間(現在時間)

期望建立一個結果集從10天前日期到現在，一樣是使用LEVEL在當Offset的時間

範例三

CONNECT BY 最強大的地方是在於建立階層表

樣本資料：

CREATE TABLE HierarchyDemo (
       PartNo INT,
       NAME VARCHAR2(16),
       ParentPartNo INT
);

INSERT INTO HierarchyDemo VALUES(1,'Boss',0);
INSERT INTO HierarchyDemo VALUES(2,'Jack',1);
INSERT INTO HierarchyDemo VALUES(3,'TOM',2);
INSERT INTO HierarchyDemo VALUES(4,'AMY',3);
INSERT INTO HierarchyDemo VALUES(5,'Daniel',2);

SQL腳本：

SELECT t1.*,LEVEL
FROM HierarchyDemo t1
START WITH ParentPartNo = 0
CONNECT BY PRIOR PartNo = ParentPartNo

START WITH ParentPartNo = 0 設置為起始點，開始找尋建立子階級

PRIOR PartNo 代表下一次由PartNo 當作根結點 找尋 PartNo = ParentPartNo 的列

最後變成下圖

    | PARTNO |   NAME | PARENTPARTNO | LEVEL |
    |--------|--------|--------------|-------|
    |      1 |   Boss |            0 |     1 |
    |      2 |   Jack |            1 |     2 |
    |      3 |    TOM |            2 |     3 |
    |      4 |    AMY |            3 |     4 |
    |      5 | Daniel |            2 |     3 |

參考資料：Oracle Hierarchical Queries

__此文作者__：Daniel Shih(石頭)
__此文地址__： https://isdaniel.github.io/oracle-connect-by/
__版權聲明__：本博客所有文章除特別聲明外，均採用 CC BY-NC-SA 3.0 TW 許可協議。轉載請註明出處！