oracle字段拆分数据重复,如何高效能拆分一个字段为多行

with t1 as

(select c1, c2, v_level

from ((select 3 c1, 'eee,fff,ggg' c2

from dual

UNION ALL

select 2 c1, 'ccc,ddd' c2

from dual

UNION ALL

SELECT 1 c1, 'aaa,bbb' c2 FROM dual)),

(select level as v_level

from dual

connect by level <=

(select max(length(c2) - length(replace(c2, ','))) + 1

from (select 3 c1, 'eee,fff,ggg' c2

from dual

UNION ALL

select 2 c1, 'ccc,ddd' c2

from dual

UNION ALL

SELECT 1 c1, 'aaa,bbb' c2 FROM dual))))

select c1,

v_level,

c2,

substr(c2,

decode(v_level,

1,

1,

instr(c2, ',', 1, decode(v_level - 1, 0, 1, v_level - 1)) + 1),

(decode(instr(c2, ',', 1, v_level),

0,

length(c2),

instr(c2, ',', 1, v_level) - 1) -

decode(v_level,

1,

0,

instr(c2,

',',

1,

decode(v_level - 1, 0, 1, v_level - 1)))))

from t1

where v_level <= length(c2) - length(replace(c2, ',')) + 1;

不知道能看到不