資料庫記錄中以逗號分隔值的形式組織數據，在尋求表格表示時會帶來挑戰。本文探討了多種高效率將此類資料轉換為行的SQL技術，方便資料擷取與操作。
考慮以下表格結構，其中value列包含逗號分隔值：
<code>CREATE TABLE tbl1 ( id NUMBER, value VARCHAR2(50) ); INSERT INTO tbl1 VALUES (1, 'AA, UT, BT, SK, SX'); INSERT INTO tbl1 VALUES (2, 'AA, UT, SX'); INSERT INTO tbl1 VALUES (3, 'UT, SK, SX, ZF');</code>
目標是將此資料轉換為表格格式，將每個值分隔到各自的行中：
<code>ID | VALUE ------------- 1 | AA 1 | UT 1 | BT 1 | SK 1 | SX 2 | AA 2 | UT 2 | SX 3 | UT 3 | SK 3 | SX 3 | ZF</code>
一個方法是利用Oracle的REGEXP_SUBSTR和CONNECT BY子句：
<code>SELECT DISTINCT id, TRIM(REGEXP_SUBSTR(value, '[^,]+', 1, level) ) VALUE, LEVEL FROM tbl1 CONNECT BY REGEXP_SUBSTR(value, '[^,]+', 1, LEVEL) IS NOT NULL ORDER BY id, LEVEL;</code>
此方法使用正規表示式(REGEXP_SUBSTR)來擷取每個子字串，而CONNECT BY遞歸地迭代逗號分隔的值。
另一種技術是使用帶有遞歸聯合的公共表表達式(CTE)：
<code>WITH t (id, res, val, lev) AS ( SELECT id, TRIM(REGEXP_SUBSTR(value, '[^,]+', 1, 1)) RES, VALUE AS VAL, 1 AS LEV FROM tbl1 WHERE REGEXP_SUBSTR(VALUE, '[^,]+', 1, 1) IS NOT NULL UNION ALL SELECT id, TRIM(REGEXP_SUBSTR(VAL, '[^,]+', 1, LEV + 1)) RES, VAL, LEV + 1 AS LEV FROM t WHERE REGEXP_SUBSTR(VAL, '[^,]+', 1, LEV + 1) IS NOT NULL ) SELECT id, res, lev FROM t ORDER BY id, lev;</code>
此方法使用遞歸CTE將逗號分隔的值分解成單一元素。
第三種解決方案採用帶有INSTR函數的遞歸CTE來識別每個子字串的起始和結束位置：
<code>WITH t (id, value, start_pos, end_pos) AS
(SELECT id, VALUE, 1, INSTR(VALUE, ',')
FROM tbl1
UNION ALL
SELECT id,
VALUE,
end_pos + 1,
INSTR(VALUE, ',', end_pos + 1)
FROM t
WHERE end_pos > 0
)
SELECT id,
SUBSTR(VALUE, start_pos, DECODE(end_pos, 0, LENGTH(VALUE) + 1, end_pos) - start_pos) AS VALUE
FROM t
ORDER BY id,
start_pos;</code>此方法利用INSTR遞歸地決定每個子字串的位置並相應地提取它們。
以上是如何在 Oracle SQL 中有效地將逗號分隔值轉換為行？的詳細內容。更多資訊請關注PHP中文網其他相關文章！
