如何基于带前导数字的两个ID列实现精准表关联？

问题根源 #

你当前使用的LIKE CONCAT('%', t1.Id)会匹配EventUID中任意位置包含Id值的记录，比如EventUID 110011234里的1234虽然出现在末尾，但它属于完整ID 11234的一部分，因此错误关联了Id 1234。我们需要确保只有当Id是EventUID的完整后缀业务ID时才建立关联。

针对性解决方案 #

根据你提供的字段特征（EventUID固定9位、无前导零的Id最长7位），分三种场景给出写法：

场景1：已知EventUID的前缀固定长度 #

如果确认EventUID的前导1/0前缀是固定长度（比如前4位），直接截取后缀并去除前导零后做等值匹配：

-- SQL Server 版本
SELECT t1.[Id] 
FROM Table1 t1
JOIN Table2 t2 ON LTRIM(SUBSTRING(t2.EventUID, 5, 5), '0') = t1.Id

-- MySQL 版本
SELECT t1.`Id` 
FROM Table1 t1
JOIN Table2 t2 ON TRIM(LEADING '0' FROM SUBSTRING(t2.EventUID, 5, 5)) = t1.Id

场景2：未知前缀长度（前缀是任意数量的1和0） #

如果前缀长度不固定，通过正则定位第一个非0/1的字符，提取后面的完整ID部分：

-- SQL Server 版本
SELECT t1.[Id] 
FROM Table1 t1
JOIN Table2 t2 ON 
    SUBSTRING(t2.EventUID, PATINDEX('%[^01]%', t2.EventUID), 9 - PATINDEX('%[^01]%', t2.EventUID) + 1) = t1.Id

• 原理：PATINDEX('%[^01]%', t2.EventUID)定位到EventUID中第一个非0/1的字符位置（即原始业务ID的起始位），从此位置截取到末尾的字符串就是完整业务ID，直接和Table1.Id等值匹配。

场景3：字段为数值类型时的简化写法 #

如果Table1.Id和Table2.EventUID都是数值类型，可通过取模运算实现精准匹配：

-- SQL Server 版本
SELECT t1.[Id] 
FROM Table1 t1
JOIN Table2 t2 ON t2.EventUID % POWER(10, LEN(t1.Id)) = t1.Id

• 原理：取EventUID对10^Id长度取模，得到的结果就是EventUID的最后N位数值（N等于Id的长度），和Id数值直接比较，彻底避免部分匹配。

验证效果 #

以你提到的错误案例为例：

• 当Id为1234时，上述写法只会匹配EventUID最后4位恰好是1234且属于原始业务ID的记录（如110001234）；
• EventUID 110011234的完整业务ID是11234，只会和Id 11234关联，不会再错误匹配1234。

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