很简单,直接执行以下语句即可。
WITH
A AS (
SELECT '1' AID,'1,2,3' 主管领导 FROM DUAL UNION ALL
SELECT '2' AID,'2,4' 主管领导 FROM DUAL),
B AS (
SELECT '1' BID,'李一' 姓名 FROM DUAL UNION ALL
SELECT '2' BID,'刘二' 姓名 FROM DUAL UNION ALL
SELECT '3' BID,'张三' 姓名 FROM DUAL UNION ALL
SELECT '4' BID,'李四' 姓名 FROM DUAL
)
SELECT A.AID 序号,A.主管领导,LISTAGG(B.姓名,',') WITHIN GROUP (ORDER BY B.BID) 主管领导姓名 FROM A LEFT JOIN B ON INSTR(','||A.主管领导||',',','||B.BID||',') > 0
GROUP BY A.AID,A.主管领导 ;
研究了一下啊上午被后面的‘,’迷惑了
写成这样是不是清晰了很多?
select aid, 主管领导,
(select listagg(b.姓名, ',') within group(order by bid)
from b
where instr( a.主管领导, b.bid ) > 0) 主管领导姓名
from a
select aid, 主管领导,
(select listagg(b.姓名, ',') within group(order by bid)
from b
where a.主管领导 like ‘%’||b.bid||'%' > 0) 主管领导姓名
from a
少个11g以前的:
with a(aid,aa)
as (
select 1,'1,2,3' from dual
union all select 2,'2,4' from dual
),
b(bid,bb)
as (
select 1,'lee' from dual
union all select 2,'liu' from dual
union all select 3,'zhang' from dual
union all select 4,'si' from dual
)
select aid,aa,to_char(substr(WMSYS.WM_CONCAT(bb),1,1000)) aa_name
from a,b
where instr(','||a.aa||',',','||b.bid||',')>0
group by aid,aa
select aid, 主管领导,
(select listagg(b.姓名, ',') within group(order by bid)
from b
where instr(',' || a.主管领导 || ',', ',' || b.bid || ',') > 0) 主管领导姓名
from a
with a as(select '1' aid,'1,2,3' 主管领导 from dual union all select '2','2,4' from dual),
b as(select '1' bid,'李一' 姓名 from dual union all select '2','刘二' from dual union all
select '3' ,'张三' from dual union all select '4','李四' from dual),
aa as(select aid,regexp_substr(主管领导,'[^,]',1,level) 主管领导
from a
connect by level<=regexp_count(主管领导,',')+1
and prior aid=aid
and prior sys_guid() is not null),
ab as(select aa.aid,aa.主管领导,b.姓名
from aa,b
where aa.主管领导=b.bid)
select aid,listagg(主管领导,',')within group(order by 主管领导) 主管领导,
listagg(姓名,',')within group(order by 主管领导) 姓名
from ab
group by aid