SQL Server 2022中如何批量查询多个MD5值的文件存在状态

可以直接返回每个传入MD5值的存在状态，推荐用行式结果（每个MD5对应一行），灵活性更高，客户端处理更方便，以下是具体实现方式：

方法1：使用表值构造函数定义待查MD5列表 #

适合提前明确所有要查询的MD5值的场景：

-- 替换VALUES中的内容为客户端传入的实际MD5值
WITH QueryMd5s AS (
    SELECT Md5 AS QueryMd5
    FROM (VALUES 
        ('xxx1'), 
        ('xxx2'), 
        ('xxxxx1'), -- 数据库中存在的MD5
        ('xxxxx4')  -- 数据库中不存在的MD5
    ) AS T(Md5)
)
SELECT 
    q.QueryMd5,
    -- 匹配到则返回1，否则返回0
    CASE WHEN f.Md5 IS NOT NULL THEN 1 ELSE 0 END AS IsExists
FROM QueryMd5s q
LEFT JOIN file f ON q.QueryMd5 = f.Md5;

执行后结果示例：

方法2：处理逗号分隔的MD5字符串参数 #

如果客户端传入的是逗号分隔的MD5字符串，可借助STRING_SPLIT函数拆分后处理：

-- 客户端传入的MD5字符串
DECLARE @InputMd5s NVARCHAR(MAX) = 'xxx1,xxx2,xxxxx1,xxxxx3';

SELECT 
    s.value AS QueryMd5,
    CASE WHEN f.Md5 IS NOT NULL THEN 1 ELSE 0 END AS IsExists
FROM STRING_SPLIT(@InputMd5s, ',') s
LEFT JOIN file f ON s.value = f.Md5;

可选：列式结果（每个MD5作为一列） #

如果需要将每个MD5作为单独的列返回，需使用动态SQL配合PIVOT实现（适合固定数量MD5的场景，或动态生成列）：

DECLARE @InputMd5s NVARCHAR(MAX) = 'xxx1,xxx2,xxxxx1';
DECLARE @PivotColumns NVARCHAR(MAX);
DECLARE @Sql NVARCHAR(MAX);

-- 生成PIVOT所需的列名
SELECT @PivotColumns = STRING_AGG(QUOTENAME(value), ',')
FROM STRING_SPLIT(@InputMd5s, ',');

-- 构造动态SQL语句
SET @Sql = N'
WITH QueryMd5s AS (
    SELECT value AS QueryMd5
    FROM STRING_SPLIT(@InputMd5s, '','')
),
ExistsStatus AS (
    SELECT 
        q.QueryMd5,
        CASE WHEN f.Md5 IS NOT NULL THEN 1 ELSE 0 END AS IsExists
    FROM QueryMd5s q
    LEFT JOIN file f ON q.QueryMd5 = f.Md5
)
SELECT ' + @PivotColumns + N'
FROM ExistsStatus
PIVOT (
    MAX(IsExists) FOR QueryMd5 IN (' + @PivotColumns + N')
) AS PivotResult;';

-- 执行动态SQL
EXEC sp_executesql @Sql, N'@InputMd5s NVARCHAR(MAX)', @InputMd5s;

执行后结果示例：

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