旧表tbl1
ID YUANGONG
10 6057863,6081791,6102168
11 6045986,6102305,6086817
12 6052191,6041969
13 6072396,6077206,6051936,6091173如何根据YUANGONG字段中的“,”来拆分保存到新表tbl2中去?新表tbl2ID YUANGONG
10 6057863
10 6081791
10 6102168
11 6045986
11 6102305
11 6086817
.......
13 6091173请大家帮忙看下
ID YUANGONG
10 6057863,6081791,6102168
11 6045986,6102305,6086817
12 6052191,6041969
13 6072396,6077206,6051936,6091173如何根据YUANGONG字段中的“,”来拆分保存到新表tbl2中去?新表tbl2ID YUANGONG
10 6057863
10 6081791
10 6102168
11 6045986
11 6102305
11 6086817
.......
13 6091173请大家帮忙看下
start with
递归调用。
select id,regexp_substr(YUANGONG,'[^,]+',1,n)
from tb1 left join
(select level from dual connect by level < 10/*这里自己调整一下,用SQL也能做到,但是没有太大意义*/)nn on 1=1
where regexp_substr(YUANGONG,'[^,]+',1,n) is not null
select id,regexp_substr(YUANGONG,'[^,]+',1,n)
from tb1 left join
(select level,rownum n from dual connect by level < 15)nn on 1=1
where regexp_substr(YUANGONG,'[^,]+',1,n) is not null;