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

此範例使用 LEVELCONNECT 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 許可協議。轉載請註明出處!


如果本文對您幫助很大,可街口支付斗內鼓勵石頭^^