如何连接两张表，对|分隔的多值字段完成映射替换？

核心思路 #

• 先将第一张表的ciaps字段按|分隔符拆分为多行单条code数据
• 拆分后的code关联第二张表的cod字段，匹配到对应的描述内容
• 按id分组，将同组的描述内容按原有顺序用|拼接回单个字段，得到最终结果

各数据库实现示例 #

以下示例中的表1、表2请替换为你实际的业务表名：

MySQL 8.0+ #

SELECT 
  t1.id,
  GROUP_CONCAT(t2.`desc` ORDER BY FIND_IN_SET(t2.cod, REPLACE(t1.ciaps, '|', ',')) SEPARATOR '|') AS ciaps
FROM 表1 t1
JOIN 表2 t2 ON FIND_IN_SET(t2.cod, REPLACE(t1.ciaps, '|', ',')) > 0
GROUP BY t1.id;

PostgreSQL #

SELECT 
  t1.id,
  STRING_AGG(t2.desc, '|' ORDER BY ord) AS ciaps
FROM 表1 t1,
     UNNEST(STRING_TO_ARRAY(t1.ciaps, '|')) WITH ORDINALITY AS s(cod, ord)
JOIN 表2 t2 ON s.cod = t2.cod
GROUP BY t1.id;

Oracle 11g+ #

SELECT 
  t1.id,
  LISTAGG(t2.desc, '|') WITHIN GROUP (ORDER BY lvl) AS ciaps
FROM 表1 t1
CROSS JOIN (
  SELECT LEVEL lvl, REGEXP_SUBSTR(t1.ciaps, '[^|]+', 1, LEVEL) cod 
  FROM DUAL 
  CONNECT BY REGEXP_SUBSTR(t1.ciaps, '[^|]+', 1, LEVEL) IS NOT NULL
) s
JOIN 表2 t2 ON s.cod = t2.cod
GROUP BY t1.id;

内容的提问来源于stack exchange，提问作者Italo Rodrigo