使用LIKE关联SQL表出现重复数据，求正确关联方案

问题根源 #

你之前用LIKE关联时出现重复，是因为模糊匹配没有限制前缀的完整性：比如abc会匹配abc_20230101，而abc_TR也会因为包含abc字符串，错误匹配到abc_20230101（反过来如果有abc_TR_20230101，abc也会匹配它），导致重复数据。

可行SQL脚本 #

方案1：精确前缀匹配（适用于Table1.FileName不含下划线的场景） #

通过强制Table2的文件名以Table1的文件名开头，且紧跟下划线+8位数字日期，确保匹配的唯一性：

-- SQL Server版本
SELECT t1.FileName, t2.TimeSentIn
FROM Table1 t1
INNER JOIN Table2 t2 
    ON t2.FileName LIKE CONCAT(t1.FileName, '_%')
    -- 验证下划线后的8位是数字日期（YYYYMMDD格式）
    AND SUBSTRING(t2.FileName, LEN(t1.FileName) + 2, 8) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

方案2：提取文件名前缀精确匹配（通用场景，支持Table1.FileName含下划线） #

提取Table2文件名中第一个下划线前的部分，与Table1的FileName完全相等，同时验证后缀是有效日期：

-- SQL Server版本
SELECT t1.FileName, t2.TimeSentIn
FROM Table1 t1
INNER JOIN Table2 t2 
    ON LEFT(t2.FileName, CHARINDEX('_', t2.FileName) - 1) = t1.FileName
    -- 验证下划线后的8位是有效日期
    AND ISDATE(SUBSTRING(t2.FileName, CHARINDEX('_', t2.FileName) + 1, 8)) = 1

适配MySQL版本 #

如果使用MySQL，替换对应的字符串函数：

-- MySQL版本
SELECT t1.FileName, t2.TimeSentIn
FROM Table1 t1
INNER JOIN Table2 t2 
    ON LEFT(t2.FileName, INSTR(t2.FileName, '_') - 1) = t1.FileName
    -- 验证后缀是有效YYYYMMDD格式日期
    AND STR_TO_DATE(SUBSTRING(t2.FileName, INSTR(t2.FileName, '_') + 1, 8), '%Y%m%d') IS NOT NULL

补充：筛选唯一匹配记录 #

如果Table2中存在同一个Table1.FileName对应多条日期记录，可添加窗口函数筛选最新/最早的TimeSentIn：

-- SQL Server示例：取每个FileName的最新TimeSentIn
WITH RankedData AS (
    SELECT 
        t1.FileName, 
        t2.TimeSentIn,
        ROW_NUMBER() OVER (PARTITION BY t1.FileName ORDER BY t2.TimeSentIn DESC) AS rn
    FROM Table1 t1
    INNER JOIN Table2 t2 
        ON LEFT(t2.FileName, CHARINDEX('_', t2.FileName) - 1) = t1.FileName
        AND ISDATE(SUBSTRING(t2.FileName, CHARINDEX('_', t2.FileName) + 1, 8)) = 1
)
SELECT FileName, TimeSentIn
FROM RankedData
WHERE rn = 1;

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