基于GroupID分组的多列匹配SQL查询问题求解

之前编写的SQL存在核心逻辑偏差：仅校验了单条记录内的两个零件字段是否匹配目标产品BOM，没有实现业务要求的「按GroupID分组后，组内所有去重零件全部命中BOM才判定匹配」的规则，缺少分组维度的全量零件校验，因此无法得到预期结果。

• 第一步：提前提取指定查询产品的全量BOM零件，作为匹配基准集合
• 第二步：遍历机器零件关联表的所有记录，标记每条记录中两个零件字段是否属于BOM集合
• 第三步：按Machine+GroupID维度分组，统计组内存在不匹配零件的记录数
• 第四步：筛选组内无不匹配零件的分组，返回去重后的机器及对应GroupID结果

WITH TargetProductParts AS (
    -- 提取查询产品的全部BOM零件，替换WHERE后的产品名即可查询其他产品适配结果
    SELECT Part
    FROM [Table 2]
    WHERE Product = 'Prod A'
),
GroupMatchCheck AS (
    SELECT
        Machine,
        GroupID,
        SUM(
            CASE 
                WHEN EXISTS (SELECT 1 FROM TargetProductParts WHERE Part = t.[Part Group 1])
                 AND EXISTS (SELECT 1 FROM TargetProductParts WHERE Part = t.[Part Group 2])
                THEN 0 ELSE 1 
            END
        ) AS unmatch_part_count
    FROM [Table 1] t
    GROUP BY Machine, GroupID
)
SELECT DISTINCT Machine, GroupID
FROM GroupMatchCheck
WHERE unmatch_part_count = 0

执行上述SQL查询Prod A时，仅Mach B对应的GroupID=2分组下所有零件均在Prod A的BOM清单中，unmatch_part_count统计值为0，会作为结果返回；其余分组只要存在任意零件不在BOM清单中，就会被过滤，完全匹配预期结果。

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