仅当CROSS APPLY返回单个值时更新记录的实现方法

要实现仅当第一个CROSS APPLY返回**唯一的mp.[id]**时才更新[processed_inventory]表，可以通过在查询中增加唯一性校验逻辑，过滤出仅存在唯一匹配项的记录后再执行更新。以下是两种可行的实现方案：

方案一：基于分组统计的校验 #

UPDATE p
SET p.[fID] = sq.[id]
FROM [processed_inventory] AS p
CROSS APPLY (
    -- 筛选符合条件的mp.id并统计每个id的匹配次数
    SELECT mp.[id], COUNT(*) AS match_count
    FROM [merged_products] AS mp
    CROSS APPLY STRING_SPLIT(mp.[M_Num], ';') AS ss
    WHERE 
        ss.[value] = p.[MNUMBER] AND
        (
            (p.[WIDTH] <  30 AND mp.[File] = '1') OR
            (p.[WIDTH] >= 30 AND mp.[File] = '2')
        ) AND (
            (p.[Arg] = 'CL'   AND mp.[Arg] = 'Coil') OR
            (p.[Arg] = ''     AND mp.[Arg] = 'Coil') OR
            (p.[Arg] IS NULL  AND mp.[Arg] = 'Coil')
        ) AND
        p.[WIDTH] = CAST(mp.[Width] AS Decimal(20,4))
    GROUP BY mp.[id]
    -- 仅保留唯一匹配的id
    HAVING COUNT(*) = 1
) AS sq
-- 额外校验每条待更新记录仅对应唯一的mp.id
INNER JOIN (
    SELECT 
        p_inner.[MNUMBER], 
        p_inner.[WIDTH], 
        ISNULL(p_inner.[Arg], '') AS Arg,
        COUNT(DISTINCT sq_inner.[id]) AS unique_id_count
    FROM [processed_inventory] AS p_inner
    CROSS APPLY (
        SELECT mp.[id]
        FROM [merged_products] AS mp
        CROSS APPLY STRING_SPLIT(mp.[M_Num], ';') AS ss
        WHERE 
            ss.[value] = p_inner.[MNUMBER] AND
            (
                (p_inner.[WIDTH] <  30 AND mp.[File] = '1') OR
                (p_inner.[WIDTH] >= 30 AND mp.[File] = '2')
            ) AND (
                (p_inner.[Arg] = 'CL'   AND mp.[Arg] = 'Coil') OR
                (p_inner.[Arg] = ''     AND mp.[Arg] = 'Coil') OR
                (p_inner.[Arg] IS NULL  AND mp.[Arg] = 'Coil')
            ) AND
            p_inner.[WIDTH] = CAST(mp.[Width] AS Decimal(20,4))
    ) AS sq_inner
    WHERE 
        p_inner.fID = 0 AND
        (p_inner.[Arg] = 'CL' OR p_inner.[Arg] = '' OR p_inner.[Arg] IS NULL)
    GROUP BY p_inner.[MNUMBER], p_inner.[WIDTH], ISNULL(p_inner.[Arg], '')
    HAVING COUNT(DISTINCT sq_inner.[id]) = 1
) AS unique_check ON 
    p.[MNUMBER] = unique_check.[MNUMBER] AND
    p.[WIDTH] = unique_check.[WIDTH] AND
    ISNULL(p.[Arg], '') = unique_check.[Arg]
WHERE 
    p.fID = 0 AND
    (p.[Arg] = 'CL' OR p.[Arg] = '' OR p.[Arg] IS NULL)

核心逻辑： #

1. 第一个子查询通过GROUP BY和HAVING确保单个mp.id仅匹配一次
2. 新增的unique_check子查询校验每条待更新的p记录仅对应一个唯一的mp.id
3. 通过INNER JOIN过滤出符合双重唯一条件的记录，再执行更新

方案二：基于窗口函数的简化校验 #

UPDATE p
SET p.[fID] = sq.[id]
FROM [processed_inventory] AS p
CROSS APPLY (
    SELECT 
        mp.[id],
        -- 统计当前p对应的所有匹配记录总数
        COUNT(*) OVER () AS total_matches,
        -- 统计当前mp.id的匹配次数
        COUNT(*) OVER (PARTITION BY mp.[id]) AS id_match_count
    FROM [merged_products] AS mp
    CROSS APPLY STRING_SPLIT(mp.[M_Num], ';') AS ss
    WHERE 
        ss.[value] = p.[MNUMBER] AND
        (
            (p.[WIDTH] <  30 AND mp.[File] = '1') OR
            (p.[WIDTH] >= 30 AND mp.[File] = '2')
        ) AND (
            (p.[Arg] = 'CL'   AND mp.[Arg] = 'Coil') OR
            (p.[Arg] = ''     AND mp.[Arg] = 'Coil') OR
            (p.[Arg] IS NULL  AND mp.[Arg] = 'Coil')
        ) AND
        p.[WIDTH] = CAST(mp.[Width] AS Decimal(20,4))
) AS sq
WHERE 
    p.fID = 0 AND
    (p.[Arg] = 'CL' OR p.[Arg] = '' OR p.[Arg] IS NULL)
    -- 确保总匹配数为1，且当前id仅匹配一次
    AND sq.total_matches = 1 AND sq.id_match_count = 1

核心逻辑： #

1. 使用窗口函数COUNT(*) OVER ()获取当前p的总匹配记录数
2. 使用COUNT(*) OVER (PARTITION BY mp.[id])统计单个mp.id的匹配次数
3. 通过sq.total_matches = 1 AND sq.id_match_count = 1直接过滤出唯一匹配的场景

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