SQL多表关联下同义不同值状态字段的匹配筛选方法

核心逻辑分三步，不依赖任何方言专属函数，Mode环境可以直接适配：

• 先把两张表的状态字段按已知对应关系，映射到同一套标准语义值，消除用词差异
• 用两表共有的Number字段做关联键关联数据
• 筛选映射后标准语义值不一致的记录，就是需要的语义不匹配结果

具体实现代码 #

用最通用的CASE表达式做值映射即可，兼容性最好：

SELECT
  t1.Name,
  t1.Number,
  t1.Status AS table1_origin_status,
  t2.Status AS table2_origin_status
FROM (
  -- 标准化表1状态值
  SELECT
    Name,
    Number,
    Status,
    CASE Status
      WHEN 'available' THEN 'std_available'
      WHEN 'unavailable' THEN 'std_unavailable'
      WHEN 'inactive' THEN 'std_inactive'
    END AS std_status
  FROM table_1
) t1
INNER JOIN (
  -- 按同一套标准映射表2状态值
  SELECT
    Number,
    Status,
    CASE Status
      WHEN 'unassigned' THEN 'std_available'
      WHEN 'unavailable' THEN 'std_unavailable'
      WHEN 'retired' THEN 'std_inactive'
    END AS std_status
  FROM table_2
) t2
  ON t1.Number = t2.Number
-- 过滤语义不匹配的记录
WHERE t1.std_status != t2.std_status

补充说明 #

• 映射后的标准值可以自定义，比如用数字编码1/2/3也可以，只要保证同义状态映射后的结果完全一致即可，示例里加std_前缀只是为了和原始值做区分，避免混淆
• 如果后续同义状态的对应关系变多，不用硬写CASE，可以单独建一张状态映射配置表存两表状态值和标准值的对应关系，关联时直接JOIN配置表做标准化，后续维护只需要改配置表数据就行
• 如果需要保留没有匹配到Number的记录，可以把INNER JOIN换成LEFT JOIN/FULL JOIN，按需调整即可

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