SQL Server 如何查询并排除文件名部分匹配的重复记录

你原有的查询存在几个核心问题：

• 把LEFT()、SUBSTRING()等函数写在了LIKE的字符串引号内部，SQL会将其识别为普通文本做匹配，不会执行函数逻辑，完全达不到预期效果
• 统计重复数的子查询没有和外层行做关联，统计的是全表计数而非当前行对应的匹配计数
• HAVING子句的写法不符合语法规范，LIKE返回的是布尔值，不能直接和数字做大小比较

核心逻辑是先提取每个文件名里的公共匹配片段作为分组键，再按分组键统计重复项、筛选去重结果，性能远高于模糊匹配方案。

1. 查询所有重复记录 #

先通过CTE提取每个文件的匹配键，再分组统计重复项：

WITH FileMatchCTE AS (
    SELECT 
        fileName,
        [Date],
        -- 这里调整SUBSTRING的参数适配你实际的匹配片段规则
        -- 示例逻辑为从第8位开始截取，截取长度为总长度减10，你可以根据实际文件名结构修改
        SUBSTRING(fileName, 8, LEN(fileName) - 10) AS match_key
    FROM 你的实际表名 -- 替换为你的业务表名，不要用database做别名避免和关键字冲突
)
SELECT 
    match_key,
    COUNT(*) AS duplicate_count,
    STRING_AGG(fileName, ' | ') AS duplicate_file_list
FROM FileMatchCTE
GROUP BY match_key
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC

2. 排除重复项输出报表 #

如果要每个匹配片段只保留一条记录，用窗口函数实现：

WITH FileMatchCTE AS (
    SELECT 
        fileName,
        [Date],
        SUBSTRING(fileName, 8, LEN(fileName) - 10) AS match_key,
        -- 同一个匹配键的记录按日期倒序排序，保留最新的一条，可自行修改排序规则
        ROW_NUMBER() OVER (PARTITION BY SUBSTRING(fileName, 8, LEN(fileName) - 10) ORDER BY [Date] DESC) AS rn
    FROM 你的实际表名
)
SELECT fileName, [Date]
FROM FileMatchCTE
WHERE rn = 1 -- 每个匹配键只取第一条
ORDER BY [Date] DESC

• 如果匹配片段的位置不固定，可以结合CHARINDEX函数定位片段的起止位置，比如要截取第一个-之后的部分可以写为SUBSTRING(fileName, CHARINDEX('-', fileName) + 1, 截取长度)
• 需确认SUBSTRING的参数设置，保证提取出的match_key就是你要匹配的相同文件名片段即可

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