SQL如何查询Table_B中diagnosis（含逗号分隔值）至少一个在Table_A的记录

你原来的SQL只能实现Table_B.diagnosis整值完全等于Table_A.Diagnosis的匹配，遇到逗号分隔的多诊断值场景就会失效，可以用以下两种方案实现需求：

方案1：字符串模糊匹配（兼容大部分数据库） #

通过补全逗号前后缀避免短编码误匹配长编码的问题，写法如下：

SELECT b.* 
FROM Table_B b
INNER JOIN Table_A a 
  ON CONCAT(',', b.diagnosis, ',') LIKE CONCAT('%,', a.Diagnosis, ',%')

适用场景：数据量小、不需要高频执行的查询，不需要修改原有表结构。
注意事项：需要保证两边的诊断值大小写、前后空格完全一致，否则会匹配失败。

方案2：拆分多值后精确匹配（性能更优、匹配更准确） #

将Table_B中逗号分隔的诊断值拆分为单行后再和Table_A做等值匹配，不同数据库的拆分函数不同，常见数据库的实现如下：

• MySQL 8.0及以上版本：

SELECT DISTINCT b.*
FROM Table_B b
JOIN JSON_TABLE(
  CONCAT('["', REPLACE(b.diagnosis, ',', '","'), '"]'),
  '$[*]' COLUMNS(diag_code VARCHAR(255) PATH '$')
) t
JOIN Table_A a ON t.diag_code = a.Diagnosis

• PostgreSQL：

SELECT DISTINCT b.*
FROM Table_B b,
unnest(string_to_array(b.diagnosis, ',')) AS t(diag_code)
JOIN Table_A a ON t.diag_code = a.Diagnosis

• SQL Server 2016及以上版本：

SELECT DISTINCT b.*
FROM Table_B b
CROSS APPLY STRING_SPLIT(b.diagnosis, ',') t
JOIN Table_A a ON t.value = a.Diagnosis

说明：代码中的DISTINCT是为了避免单条Table_B记录匹配到多个Table_A诊断时出现重复行，如果需要保留所有匹配到的诊断项可以删除该关键字。

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