在oracle中將一行字元串拆分成多行

例如，有如下一張表，表名為bk_test。插入了以下數據:

CREATE TABLE BK_TESK(id varchar2(10),s varchar2(20));
insert into BK_TESK values ('A','1,2,3');
insert into BK_TESK values ('B','4,5,6');
insert into BK_TESK values ('C','7,8');

查詢數據如圖所示

我們需要將欄位S中以逗號分隔的數據拆分成多行。結果如圖所示

實現將一行數據拆分成多行的代碼如下所示

SELECT
    id,
    TRIM(REGEXP_SUBSTR(s, '[^,]+', 1, LEVEL)) AS s
FROM
    BK_TESK
CONNECT BY
    PRIOR SYS_GUid() IS NOT NULL
    AND LEVEL <= REGEXP_COUNT(s, ',') + 1
    AND PRIOR id = id
ORDER BY
    id, s;

1: PRIOR id = id這個條件的主要作用是確保層次查詢在處理每個id 時保持連續性

• PRIOR 關鍵字用於引用上一行的值。
• 這個條件使得每次迭代都使用相同的 id 值。
• 它防止查詢跨越不同的 id 值，確保每個 id 的所有拆分值都被正確處理。
• 例如，當處理 'a' 的值時，這個條件確保不會跳到 'b' 的值，直到 'a' 的所有值都被處理完。
• id欄位必須是唯一值，不然查詢結果會出現重覆值。如果id不是唯一，可以使用rowid（PRIOR rowid = rowid）

2: PRIOR SYS_GUID() IS NOT NULL。這個條件的主要目的是為了創建一個始終為真的條件。允許 CONNECT BY 繼續執行，直到其他條件（如 LEVEL 條件）停止查詢