如何实现SQL两表顺序不同的specialties字段精确匹配

核心思路是将两边specialties字段的逗号分隔条目统一排序后再做等值匹配，步骤如下：

• 第一步：将specialties字段按, （逗号加空格，注意和你实际分隔符对齐）拆分为单个条目，拆分时对每个条目做去空格处理避免格式差异
• 第二步：对拆分后的所有条目按统一规则（比如字典序升序）排序
• 第三步：将排序后的条目按相同分隔符重新拼接为字符串，此时两张表条目完全一致的记录拼接结果会完全相同，直接做等值关联即可

不同数据库实现示例 #

MySQL 8.0+ 示例 #

SELECT 
  t1.userID,
  t2.*
FROM table1 t1
INNER JOIN table2 t2
ON (
  SELECT GROUP_CONCAT(TRIM(specialty) ORDER BY TRIM(specialty) SEPARATOR ', ')
  FROM JSON_TABLE(
    CONCAT('["', REPLACE(t1.specialties, ', ', '","'), '"]'),
    '$[*]' COLUMNS(specialty VARCHAR(255) PATH '$')
  ) AS t1_splits
) = (
  SELECT GROUP_CONCAT(TRIM(specialty) ORDER BY TRIM(specialty) SEPARATOR ', ')
  FROM JSON_TABLE(
    CONCAT('["', REPLACE(t2.specialties, ', ', '","'), '"]'),
    '$[*]' COLUMNS(specialty VARCHAR(255) PATH '$')
  ) AS t2_splits
)
WHERE t1.userID = 1;

PostgreSQL 示例 #

SELECT 
  t1.userID,
  t2.*
FROM table1 t1
INNER JOIN table2 t2
ON (
  SELECT STRING_AGG(TRIM(specialty), ', ' ORDER BY TRIM(specialty))
  FROM UNNEST(STRING_TO_ARRAY(t1.specialties, ', ')) AS t1_splits(specialty)
) = (
  SELECT STRING_AGG(TRIM(specialty), ', ' ORDER BY TRIM(specialty))
  FROM UNNEST(STRING_TO_ARRAY(t2.specialties, ', ')) AS t2_splits(specialty)
)
WHERE t1.userID = 1;

大数据量优化建议 #

如果表的数据量很大，关联时实时计算排序后的字符串性能偏低，可以提前在两张表上加预计算的计算列存储排序后的specialties值，对计算列建索引后再做等值关联，查询性能会提升明显。

注意事项 #

处理时要注意统一分隔符规则，比如有的记录可能用, （逗号加空格）分隔，有的可能只用,分隔，建议拆分前先做格式统一，避免因为格式差异导致匹配失败。

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