筛选匹配指定TemplateId全部条件的StoreNbr的SQL查询问题

表结构 #

示例数据集 #

StoreAttributes 数据 #

TemplateConditions 数据 #

需求 #

仅筛选出**完全匹配TemplateId=78对应所有ConditionAttributeId**的门店编号，即仅返回5705。

原语句问题 #

原SQL使用IN子查询，仅能返回至少匹配一个模板条件的门店，无法满足“全部匹配”的要求：

Select Distinct StoreNbr 
From StoreAttributes st
Where ConditionAttributeId in (Select ConditionAttributeId 
                               From TemplateConditions 
                               Where TemplateId = 78); 

解决方案 #

方法1：分组统计匹配数对比 #

通过关联门店与模板条件，分组后统计每个门店匹配的条件数量，与模板总条件数相等则判定为完全匹配：

SELECT st.StoreNbr
FROM StoreAttributes st
JOIN TemplateConditions tc 
  ON st.ConditionAttributeId = tc.ConditionAttributeId
WHERE tc.TemplateId = 78
GROUP BY st.StoreNbr
HAVING COUNT(DISTINCT st.ConditionAttributeId) = (
    SELECT COUNT(DISTINCT ConditionAttributeId)
    FROM TemplateConditions
    WHERE TemplateId = 78
);

方法2：双重NOT EXISTS排除法 #

排除存在模板条件但门店未匹配的情况，剩余即为完全匹配的门店：

SELECT DISTINCT st.StoreNbr
FROM StoreAttributes st
WHERE NOT EXISTS (
    SELECT 1
    FROM TemplateConditions tc
    WHERE tc.TemplateId = 78
    AND NOT EXISTS (
        SELECT 1
        FROM StoreAttributes st2
        WHERE st2.StoreNbr = st.StoreNbr
        AND st2.ConditionAttributeId = tc.ConditionAttributeId
    )
);

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