Oracle [CONNECT BY]

之前有介紹在Oracle 有提供一個精簡的語法產生階層資料 CONNECT BY

CONNECT BY 有幾個常用Key Word.

LEVEL目前在樹節點第幾階層
START WITH 設定哪筆做為起始點開始樹
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

一開始有兩個欄位

StartDt 起始時間(10天前)
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 許可協議。轉載請註明出處！