基于跨表部分字符串匹配的SQL查询实现方法

核心匹配逻辑非常简单：表2的人员ID固定以表1的Short字段值为前缀，不需要做全表模糊扫描，只需要提取ID中的对应前缀片段，和表1的Short字段做等值关联，即可拿到匹配的FirstName，整体查询性能远高于通配符模糊匹配。

具体实现方式 #

• 动态长度匹配（兼容缩写长度不统一的场景）
  不需要提前统计所有Short的固定长度，关联时自动根据每个缩写的实际长度，从ID左侧截取等长字符串做精准匹配，适配后续新增不同长度缩写的场景。兼容MySQL、PostgreSQL等支持LEFT()字符串截取函数的数据库，参考写法：

  SELECT 
    t1.first_name,
    t2.surname
  FROM person_id_info t2
  JOIN short_first_name_map t1
    ON LEFT(t2.id, CHAR_LENGTH(t1.short)) = t1.short
  

• 固定长度前缀匹配（已知缩写长度完全统一的场景，性能更优）
  从给出的示例数据看，所有Short值都是3位固定长度，这种场景可以直接固定截取ID前3位做关联，省去关联时的动态长度计算开销，是性能最好的实现方式，参考写法：

  SELECT 
    t1.first_name,
    t2.surname
  FROM person_id_info t2
  JOIN short_first_name_map t1
    ON SUBSTRING(t2.id, 1, 3) = t1.short
  

边界问题规避 #

如果后续出现不同长度的缩写共存的情况（比如同时存在2位缩写TM和3位缩写TMS），上述基础关联逻辑会让ID为TMS123的记录同时匹配到两个映射值，产生重复结果。这种场景需要在关联规则中追加最长匹配优先级，保证每条ID只匹配到长度最长、最精准的缩写值，避免数据冗余。

按照上述逻辑执行查询，最终输出的结果完全匹配示例的对应关系：

• Thomas Johnson（对应ID=TMS123）
• Christina Smith（对应ID=CNA342）
• Thomas Cooper（对应ID=TMS667）
• Robert Lewis（对应ID=RBT555）

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