oracle列数据拆分,Oracle随笔之用拆分后的列数据关联表查询

-----------------------建表-------------------------

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 ;

---------- -----------返回结果--------- ----------------------------------------

--1 28345|39262|56214 产品A，产品B，产品C

--2 28345|56214 产品A，产品C

--3 56214 产品C

DROP TABLE test;

DROP TABLE P;

标签：level,into,56214,id,test,拆分,Oracle,随笔,plist

来源： https://www.cnblogs.com/Bokeyan/p/11504921.html