注意正则表达式中的逗号的中英文,与你的数据一致 select station,wmsys.wm_concat(line) line from( select line,REGEXP_SUBSTR(station, '[^,]+', 1, LEVEL) station from T CONNECT BY LEVEL <= REGEXP_COUNT(station, '[^,]+') and rowid= prior rowid and prior dbms_random.value is not null ) group by station;
with tmp as ( select 300 + level as line, decode(level,1,'站点1,站点2,站点3,站点4', 2,'站点4,站点5,站点6,站点7', 3,'站点2,站点3,站点4,站点8', 4,'站点9,站点1,站点4,站点6') as station from dual connect by level <= 4 ), ds as ( select '站点'||to_char(b.rn) as station, decode(sign(instr(a.station,'站点'||to_char(b.rn))),1,a.line,null) as line from tmp a, ( select level as rn from dual connect by level <= 9 ) b ) select station,wmsys.wm_concat(line) as line from ds where line is not null group by station;
select station,wmsys.wm_concat(line) line
from(
select line,REGEXP_SUBSTR(station, '[^,]+', 1, LEVEL) station
from T
CONNECT BY LEVEL <= REGEXP_COUNT(station, '[^,]+')
and rowid= prior rowid
and prior dbms_random.value is not null
) group by station;
( select 300 + level as line,
decode(level,1,'站点1,站点2,站点3,站点4',
2,'站点4,站点5,站点6,站点7',
3,'站点2,站点3,站点4,站点8',
4,'站点9,站点1,站点4,站点6') as station
from dual connect by level <= 4 ),
ds as
( select '站点'||to_char(b.rn) as station,
decode(sign(instr(a.station,'站点'||to_char(b.rn))),1,a.line,null) as line
from tmp a,
( select level as rn from dual connect by level <= 9 ) b )
select station,wmsys.wm_concat(line) as line
from ds
where line is not null
group by station;