一个字段里多个值，如何按照每个值查询另一张表对应值再拼在一起

[花生射手]

oracle初学者，希望能得到各位的指教。
现在有两张表，A表：
代码
1001,1002,1003
1003,1005,1006
1008,1007

B表：
代码 名称
1001 A
1002 B
1003 C
1005 D
1006 E
1007 F
1008 G

然后希望能查出：
代码 名称
1001,1002,1003 A,B,C
1003,1005,1006 C,D,E
1008,1007 G,F

我看了有一个帖子跟这个内容很相似，但是更复杂，实在看不懂，希望大家能指导，谢谢。

[花开了叫我]

WITH a(代码) AS (SELECT '1001,1002,1003' FROM dual UNION ALL SELECT '1003,1005,1006' from dual UNION ALL SELECT '1008,1007' from dual), b(代码,名称) AS (SELECT '1001','A' FROM dual UNION ALL SELECT '1002','B' FROM dual UNION ALL SELECT '1003','C' FROM dual UNION ALL SELECT '1005','D' FROM dual UNION ALL SELECT '1006','E' FROM dual UNION ALL SELECT '1007','F' from dual UNION ALL SELECT '1008','G' FROM dual), c as(SELECT rm,代码,名称 FROM (SELECT rm,regexp_substr(代码,'[^,]+',1,LEVEL)代码1 FROM (SELECT 代码,ROWNUM rm FROM a) CONNECT BY LEVEL<=regexp_count(代码,',')+1 AND 代码=PRIOR 代码 AND PRIOR dbms_random.value IS NOT NULL)ar JOIN b ON ar.代码1=B.代码) SELECT listagg(代码,',')WITHIN GROUP(ORDER BY 代码) 代码,listagg(名称,',')WITHIN GROUP(ORDER BY 代码) 名称 FROM c GROUP BY rm; 其实数据就不该以表A的方式存储

[mayanzs]

select code,listagg(mc,',') with group (order by mc) from (select a.code,b.mc from a,b where instr(a.code,b.code)>0) group by code;

[mayanzs]

sorry, within 不是with

[花开了叫我]

WITH a(代码) AS (SELECT '1001,1002,1003' FROM dual UNION ALL SELECT '1003,1005,1006' from dual UNION ALL SELECT '1008,1007' from dual), b(代码,名称) AS (SELECT '1001','A' FROM dual UNION ALL SELECT '1002','B' FROM dual UNION ALL SELECT '1003','C' FROM dual UNION ALL SELECT '1005','D' FROM dual UNION ALL SELECT '1006','E' FROM dual UNION ALL SELECT '1007','F' from dual UNION ALL SELECT '1008','G' FROM dual) SELECT a.代码,(SELECT listagg(b.名称,',')WITHIN GROUP (ORDER BY b.代码) FROM b WHERE INSTR(','||a.代码||',',','||b.代码||',')>0)名称 FROM a; --看了帖子 发现这个写法更好

[丨大浣熊丨]

select A.代码,名称 from A left join B on A.代码=b.代码
结果是这样得

[花生射手]

引用 4 楼 weixin_38847048 的回复:

WITH a(代码) AS (SELECT '1001,1002,1003' FROM dual UNION ALL SELECT '1003,1005,1006' from dual UNION ALL SELECT '1008,1007' from dual), b(代码,名称) AS (SELECT '1001','A' FROM dual UNION ALL SELECT '1002','B' FROM dual UNION ALL SELECT '1003','C' FROM dual UNION ALL SELECT '1005','D' FROM dual UNION ALL SELECT '1006','E' FROM dual UNION ALL SELECT '1007','F' from dual UNION ALL SELECT '1008','G' FROM dual) SELECT a.代码,(SELECT listagg(b.名称,',')WITHIN GROUP (ORDER BY b.代码) FROM b WHERE INSTR(','||a.代码||',',','||b.代码||',')>0)名称 FROM a; --看了帖子 发现这个写法更好

非常感谢你！ 那么这个表有多少字段就得重复写多少次是吗？ 是这样的，其实A表是凭证的明细表，A的“代码”是分录的对方科目，因为对方科目太多了所以就是这样了。

[花生射手]

引用 2 楼 mayanzs 的回复:

select code,listagg(mc,',') with group (order by mc) from (select a.code,b.mc from a,b where instr(a.code,b.code)>0) group by code;

这个好像不太能写的出来，可以麻烦你标注一下第一局和最后一句的code属于哪个表吗？

[花生射手]

引用 5 楼 qq_32583275 的回复:

select A.代码,名称 from A left join B on A.代码=b.代码 结果是这样得

谢谢。 但是这个是A左链接B表，因为A代码没有和B代码相等的，所以只是显示了A表全表。

[mayanzs]

纠正一下 select code,listagg(mc,',') within group (order by mc) from (select a.code,b.mc from a,b where instr(a.code,b.code)>0) MB group by code; 这是中间表(select a.code,b.mc from a,b where instr(a.code,b.code)>0) MB 的字段