SQL查询实现：筛选无匹配PNG文件的异常PDF附件

1. 定位未生成对应PNG的异常PDF文件 #

逻辑说明：

• 筛选所有后缀为.pdf（不区分大小写）的附件作为待校验集合
• 同一表单ID下，PNG文件命名规则为「PDF文件名（不含后缀）+ 数字 + .png」，匹配时校验前缀后剩余部分为纯数字，即可兼容1~N位页码场景
• 左关联匹配PNG，关联结果为空的即为转码失败的异常PDF

SELECT DISTINCT
    tf.id AS FormId,
    a.FileName AS PdfFileName
FROM TrainingForm tf
INNER JOIN Attachment a 
    ON tf.Id = a.FormId
    AND LOWER(a.FileName) LIKE '%.pdf'
LEFT JOIN Attachment png_a
    ON tf.Id = png_a.FormId
    AND LOWER(png_a.FileName) LIKE '%.png'
    -- 前缀匹配PDF基础名（去掉.pdf后缀的部分）
    AND LEFT(REPLACE(LOWER(png_a.FileName), '.png', ''), 
             LEN(REPLACE(LOWER(a.FileName), '.pdf', ''))) 
        = REPLACE(LOWER(a.FileName), '.pdf', '')
    -- 前缀后剩余部分全为数字，兼容1位/2位/N位页码
    AND PATINDEX('%[^0-9]%', 
                 RIGHT(REPLACE(LOWER(png_a.FileName), '.png', ''),
                       LEN(REPLACE(LOWER(png_a.FileName), '.png', '')) 
                       - LEN(REPLACE(LOWER(a.FileName), '.pdf', ''))
                 )) = 0
WHERE png_a.FileName IS NULL
ORDER BY tf.CreatedOn DESC

该查询返回结果和你预期格式完全一致，仅包含转码失败的PDF表单ID和文件名。 #

2. 修复多页码PNG的原文件名匹配统计逻辑 #

原SQL问题：直接截断文件名末尾1位的逻辑仅支持1位页码，2位及以上页码会残留末尾数字，导致统计结果拆分。
正确逻辑：提取PNG文件名去掉后缀后，删除末尾所有连续数字，得到的就是对应原始PDF的基础名，按该值聚合统计即可。

SELECT
    tf.id AS FormId,
    -- 去掉PNG后缀后删除末尾所有连续数字，得到原始PDF基础名
    LEFT(
        REPLACE(LOWER(a.FileName), '.png', ''),
        LEN(REPLACE(LOWER(a.FileName), '.png', '')) 
        - PATINDEX('%[^0-9]%', REVERSE(REPLACE(LOWER(a.FileName), '.png', ''))) 
        + 1
    ) AS PdfBaseName,
    COUNT(*) AS PngCount
FROM TrainingForm tf
INNER JOIN Attachment a ON tf.Id = a.FormId
WHERE 
    LOWER(a.FileName) LIKE '%.png'
    AND DATEDIFF(dd, tf.CreatedOn, GETDATE()) < 60
GROUP BY 
    tf.id,
    LEFT(
        REPLACE(LOWER(a.FileName), '.png', ''),
        LEN(REPLACE(LOWER(a.FileName), '.png', '')) 
        - PATINDEX('%[^0-9]%', REVERSE(REPLACE(LOWER(a.FileName), '.png', ''))) 
        + 1
    )

该统计会把同一个PDF生成的所有1位、2位页码的PNG都归到同一个基础名下，不会再出现拆分问题，返回的PngCount即为该PDF对应的PNG总数量。

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