-----------------------建表-------------------------
create table test(id int, plist varchar2(30)) ;
create table p(pid int ,pname varchar2(10));
-----------------------插入测试数据----------------------------
insert into test values(1,‘28345|39262|56214‘);
insert into test values(2,‘28345|56214‘);
insert into test values(3,‘56214‘);
insert into p values(28345,‘产品A‘);
insert into p values(39262,‘产品B‘);
insert into p values(56214,‘产品C‘);
-----------------------------拆分语句及结果------------------------------------
select id, plist,level p_level, regexp_substr(plist , ‘[^|]+‘, 1, level) pid
from test
connect by level <= regexp_count(plist , ‘[^|]+‘)
and prior id = id
and prior dbms_random.value is not null
查询结果
-------------------拆分后关联并拼接字符后的处理语句-------------------
with m as (
--拆分列数据
select id, plist,level p_level, regexp_substr(plist , ‘[^|]+‘, 1, level) pid
from test
connect by level <= regexp_count(plist , ‘[^|]+‘)
and prior id = id
and prior dbms_random.value is not null
)
select m.id , m.plist, listagg(p.pname,‘,‘) within group(order by p_level) rrr
from m inner join p on m.pid = p.pid
group by m.id, m.plist ;
DROP TABLE test;
DROP TABLE P;
实战亲测可用
with m as (select nsp.id, nsp.performance_no, nsp.spot_manager_panid, level p_level, regexp_substr(nsp.spot_manager_panid, ‘[^,]+‘, 1, level) as pid from nh_sale_performance nsp start with nsp.spot_manager_panid like ‘%,%‘ and nsp.performance_no is not null connect by level <= regexp_count(nsp.spot_manager_panid, ‘[^,]+‘) and prior nsp.id = nsp.id and prior dbms_random.value is not null) select m.performance_no, m.spot_manager_panid, listagg(su.old_user_id, ‘,‘) within group(order by p_level) rrr from m inner join sys_user su on su.id = m.pid group by m.id, m.spot_manager_panid, performance_no