原始的数据:
aa,bb,cc,dd
ee,ff,gg,hh
想要得到的结果:
aa
bb
cc
dd
...
先弄一条试试:
SQL> with t as(
select 1 id,'aa,bb,cc,dd' names from dual)
select id,REGEXP_SUBSTR(names, '[^,]+', 1, level) name,
'REGEXP_SUBSTR('''||names|| ',''[^,]+'', 1, '||level||')' exec_s from t
connect by level <=regexp_count(names,',')+1
order by name;
ID NAME EXEC_S
---- ------ --------------------------------------------
1 aa REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 1)
1 bb REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 2)
1 cc REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 3)
1 dd REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
已用时间: 00: 00: 00.01
这么写发现结果集不对:
SQL> with t as(select 1 id,'aa,bb,cc,dd' names from dual
union all
select 2 id,'ee,ff,gg,hh' names from dual
)
select id,REGEXP_SUBSTR(names, '[^,]+', 1, level) name,
'REGEXP_SUBSTR('''||names|| ',''[^,]+'', 1, '||level||')' exec_s from t
connect by level <=regexp_count(names,',')+1
order by name;
ID NAME EXEC_S
--- ------ ----------------------------------------------
1 aa REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 1)
1 bb REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 2)
1 bb REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 2)
1 cc REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 3)
1 cc REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 3)
1 cc REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 3)
1 cc REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 3)
1 dd REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
1 dd REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
1 dd REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
1 dd REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
1 dd REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
1 dd REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
1 dd REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
1 dd REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
2 ee REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 1)
2 ff REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 2)
2 ff REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 2)
2 gg REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 3)
2 gg REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 3)
2 gg REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 3)
2 gg REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 3)
2 hh REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
2 hh REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
2 hh REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
2 hh REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
2 hh REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
2 hh REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
2 hh REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
2 hh REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
已选择30行。
已用时间: 00: 00: 00.06
原因是多行的话产生了上下级关系:
SQL> with t as(
select 1 id,'aa,bb,cc,dd' names from dual
union all
select 2 id,'ee,ff,gg,hh' names from dual
) select level,names,sys_connect_by_path(id,'--') as full_path,
count(1) over(partition by names,level) as cnt
from t
connect by level <= regexp_count(names,',')+1
order by names,level;
LEVEL NAMES FULL_PATH CNT
---------- ----------- -------------------- ----------
1 aa,bb,cc,dd --1 1
2 aa,bb,cc,dd --2--1 2
2 aa,bb,cc,dd --1--1 2
3 aa,bb,cc,dd --1--2--1 4
3 aa,bb,cc,dd --1--1--1 4
3 aa,bb,cc,dd --2--2--1 4
3 aa,bb,cc,dd --2--1--1 4
4 aa,bb,cc,dd --1--1--2--1 8
4 aa,bb,cc,dd --1--1--1--1 8
4 aa,bb,cc,dd --2--2--2--1 8
4 aa,bb,cc,dd --2--2--1--1 8
4 aa,bb,cc,dd --2--1--2--1 8
4 aa,bb,cc,dd --2--1--1--1 8
4 aa,bb,cc,dd --1--2--2--1 8
4 aa,bb,cc,dd --1--2--1--1 8
1 ee,ff,gg,hh --2 1
2 ee,ff,gg,hh --1--2 2
2 ee,ff,gg,hh --2--2 2
3 ee,ff,gg,hh --2--2--2 4
3 ee,ff,gg,hh --2--1--2 4
3 ee,ff,gg,hh --1--2--2 4
3 ee,ff,gg,hh --1--1--2 4
4 ee,ff,gg,hh --1--1--1--2 8
4 ee,ff,gg,hh --1--2--1--2 8
4 ee,ff,gg,hh --2--2--2--2 8
4 ee,ff,gg,hh --1--2--2--2 8
4 ee,ff,gg,hh --2--1--2--2 8
4 ee,ff,gg,hh --2--1--1--2 8
4 ee,ff,gg,hh --2--2--1--2 8
4 ee,ff,gg,hh --1--1--2--2 8
只限制本行拆分:
SQL> with t as(
select 1 id,'aa,bb,cc,dd' names from dual
union all
select 2 id,'ee,ff,gg,hh' names from dual
)
select level,names,sys_connect_by_path(id,'--') as full_path,
count(1) over(partition by names,level) as cnt from t
connect by prior names=names
and level<=regexp_count(names,',')+1
and prior SYS_GUID() is not null;
LEVEL NAMES FULL_PATH CNT
---------- ----------- -------------------- ----------
1 aa,bb,cc,dd --1 1
2 aa,bb,cc,dd --1--1 1
3 aa,bb,cc,dd --1--1--1 1
4 aa,bb,cc,dd --1--1--1--1 1
1 ee,ff,gg,hh --2 1
2 ee,ff,gg,hh --2--2 1
3 ee,ff,gg,hh --2--2--2 1
4 ee,ff,gg,hh --2--2--2--2 1
改写:
SQL> with t as(select 1 id,'aa,bb,cc,dd' names from dual
union all
select 2 id,'ee,ff,gg,hh' names from dual
)
select id,REGEXP_SUBSTR(names, '[^,]+', 1, level) name,
'REGEXP_SUBSTR('''||names|| ',''[^,]+'', 1, '||level||')' exec_s from t
connect by prior names=names
and level<=regexp_count(names,',')+1
and prior SYS_GUID() is not null
order by name;
ID NAME EXEC_S
---- ------- ---------------------------------------------
1 aa REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 1)
1 bb REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 2)
1 cc REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 3)
1 dd REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
2 ee REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 1)
2 ff REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 2)
2 gg REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 3)
2 hh REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
已选择8行。
已用时间: 00: 00: 00.05