PostgreSQL中如何匹配竖线分隔多值单元格实现两表JOIN

问题根因 #

直接使用等值关联仅能匹配单值完全一致的记录，单元格内竖线分隔的多值字符串无法和表2的单个代码值做等值匹配，因此关联结果不符合预期。

实现语句 #

不需要物理拆分表1的存储行，直接通过数组包含判断即可实现关联匹配，和预期输出完全一致的语句如下：

SELECT
  t1.Site,
  t2.Note,
  t2.CodeValue2 AS "Code Value"
FROM test.table1 t1
JOIN test.table2 t2
  ON string_to_array(t1.CodeValue1, '|') @> ARRAY[t2.CodeValue2];

写法说明 #

• string_to_array(t1.CodeValue1, '|')会在查询运行时将竖线分隔的多值字符串临时转换为数组，不会修改表1的原有存储结构，也不会将表1物理拆分为更多行。
• @>是PostgreSQL的数组包含运算符，用于判断右侧的单元素数组（即表2的单个代码值）是否存在于左侧的多值数组中，匹配逻辑为精确匹配，不会出现类似模糊查询的错配问题。
• 如果需要保留表1中未匹配到表2记录的行（例如Site为C、CodeValue为1008的记录），将语句中的JOIN替换为LEFT JOIN即可。

替代写法（不推荐） #

如果是特殊场景无法使用数组运算符，可以用字符串拼接后模糊匹配的方式实现，性能弱于数组写法，数据量较大时不建议使用：

SELECT
  t1.Site,
  t2.Note,
  t2.CodeValue2 AS "Code Value"
FROM test.table1 t1
JOIN test.table2 t2
  ON CONCAT('|', t1.CodeValue1, '|') LIKE CONCAT('%|', t2.CodeValue2, '|%');

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