我在 Oracle 中有一張表，如下所示：
KEY,VALS
k1,"a,b"
我需要它看起來像：
KEY,VAL
k1,a
k1,b
我用CONNECT BYand做了這個LEVEL，下面一個例子：
with t as (
select 'k1' as key, 'a,b' as vals
from dual
)
select key, regexp_substr(vals, '[^,] ', 1, level) as val
from t
connect by LEVEL <= length(vals) - length(replace(vals, ',')) 1
但是當我在表中有多行時，并且vals可以是不同長度的逗號分隔值，例如：
KEY,VALS
k1,"a,b"
k2,"c,d,e"
我正在尋找類似的結果：
KEY,VAL
k1,a
k1,b
k2,c
k2,d
k2,e
但是上面的幼稚方法不起作用，因為每一層都與它上面的一層相連，導致：
with t as (
select 'k1' as key, 'a,b' as vals
from dual
union
select 'k2' as key, 'c,d,e' as vals
from dual
)
select key, regexp_substr(vals, '[^,] ', 1, level) as val
from t
connect by LEVEL <= length(vals) - length(replace(vals, ',')) 1
KEY,VAL
k1,a
k1,b
k2,e
k2,d
k2,e
k2,c
k1,b
k2,e
k2,d
k2,e
我懷疑我需要某種CONNECT BY PRIOR條件，但我不確定是什么。嘗試按鍵匹配時：
connect by prior key = key
and LEVEL <= length(vals) - length(replace(vals, ',')) 1
我得到一個ORA-01436: CONNECT BY loop in user data錯誤。
這里的正確方法是什么？
uj5u.com熱心網友回復：
選項 1：簡單、快速的字串函式和遞回查詢：
with t (key, vals) as (
SELECT 'k1', 'a,b' FROM DUAL UNION ALL
SELECT 'k2', 'c,d,e' FROM DUAL
),
bounds (key, vals, spos, epos) AS (
SELECT key, vals, 1, INSTR(vals, ',', 1)
FROM t
UNION ALL
SELECT key, vals, epos 1, INSTR(vals, ',', epos 1)
FROM bounds
WHERE epos > 0
)
SEARCH DEPTH FIRST BY key SET key_order
SELECT key,
CASE epos
WHEN 0
THEN SUBSTR(vals, spos)
ELSE SUBSTR(vals, spos, epos - spos)
END AS val
FROM bounds;
選項 2：在 LATERAL 連接的分層查詢中較慢的正則運算式
此選項需要 Oracle 12 或更高版本。
with t (key, vals) as (
SELECT 'k1', 'a,b' FROM DUAL UNION ALL
SELECT 'k2', 'c,d,e' FROM DUAL
)
SELECT key, val
FROM t
LEFT OUTER JOIN LATERAL (
SELECT regexp_substr(vals, '[^,] ', 1, level) AS val
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(vals, '[^,] ')
)
ON (1 = 1)
選項 3：與父行相關的遞回查詢。
此選項是最慢的選項，因為它需要在層次結構的級別之間關聯并在每個步驟中生成一個 GUID（這看似無用，但可以防止不必要的遞回）。
with t (key, vals) as (
SELECT 'k1', 'a,b' FROM DUAL UNION ALL
SELECT 'k2', 'c,d,e' FROM DUAL
)
SELECT key,
regexp_substr(vals, '[^,] ', 1, level) AS val
FROM t
CONNECT BY LEVEL <= REGEXP_COUNT(vals, '[^,] ')
AND PRIOR key = key
AND PRIOR SYS_GUID() IS NOT NULL;
其中所有輸出：
鑰匙 價值 k1 一種 k1 b k2 C k2 d k2 e
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/430683.html