Day 15 基礎-一變多行

2024 iThome 鐵人賽

DAY 15

IT 管理

不居功的系統隱士 - 30天由淺入深學SQL系列第 15 篇

16th鐵人賽 connect by level 一變多行

團隊為你抓鯉魚

2024-09-28 22:37:11

166 瀏覽

分享至

在整理資料的時候，將不同欄位合併，可以透過||完成，但需要將同欄位的資料進行切割，則需要透過connect by來完成。

一變多行-CONNECT語法

CONNECT BY是 Oracle SQL 中用來執行階層查詢的語法(樹狀關係)：

START WITH：定義樹的根節點或起始節點
CONNECT BY PRIOR：定義父子節點關係，PRIOR 表父層值
SYS_CONNECT_BY_PATH：顯示從根到當前節點的路徑
LEVEL：目前在樹狀結構中的階層，根節點 LEVEL 為 1，子節點 LEVEL 為 2...
PRIOR：指定遞迴方向，前一層的列
NOCYCLE：確保數據中不會無限循環
ORDER SIBLINGS BY：可以在同一層級內對結果進行排序

SELECT column1, column2, ..., LEVEL,
SYS_CONNECT_BY_PATH (column2, '->') AS path
FROM table_name
START WITH condition [父節點定義]
CONNECT BY NOCYCLE PRIOR parent_column = child_column [遞迴關係定義]
ORDER column1 BY column2
;

-- 一拆多範例
select REGEXP_SUBSTR('01#02#03#04', '[^#]+', 1, rownum) as newport
from dual
;
-- output
01

select REGEXP_SUBSTR('01#02#03#04', '[^#]+', 1, rownum) as newport
from dual
connect by rownum <= REGEXP_COUNT('01#02#03#04', '[^#]+')
;
-- output
01
02
03
04

select REGEXP_SUBSTR('AB#1VG#2GH#3', '.{4}', 1, rownum) as newport
from dual
connect by level <= (length('AB#1VG#2GH#3') / 4)
;
-- output
AB#1
VG#2
GH#3

SELECT X + LEVEL
FROM (
  SELECT 0 X 
  FROM DUAL
 )
CONNECT BY LEVEL <= 10
;
-- output
     1
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	 9
	10