SQL Server查询匹配所有指定列值排除第三表记录返回空问题

查询返回空结果由两处核心逻辑错误导致：

• 原NOT IN条件做了全局过滤：只要val值在providerbreak表中存在，就会被所有provider的匹配结果排除。测试数据里providerbreak存在val=10的记录，直接把所有provider下val=10的有效记录全部筛掉了。但实际providerbreak存储的是指定provider的禁用val，比如provider2的val=10被禁用，不代表其他provider的val=10也不可用。
• 全局排除val=10后，剩余可匹配的输入值仅11、12、13三个，窗口函数统计的单provider匹配数最多为3，永远达不到输入参数总个数4，自然返回空结果。
• 原查询未关联providers主表，不符合“从providers表查询记录”的初始需求。

DECLARE @InString VARCHAR(50)= '10,11,12,13';
DECLARE @InTemp TABLE(KEYY TINYINT);
INSERT @InTemp(KEYY)
   SELECT VALUE FROM STRING_SPLIT(@InString,',')


SELECT X.PROVIDERID,X.VAL FROM
(
   SELECT P.PROVIDERID,P.val,
     COUNT(*)OVER (PARTITION BY P.PROVIDERID) XCOL
     FROM DBO.PROVIDERAVL AS P
     -- 关联providers主表
     INNER JOIN DBO.PROVIDERS PR ON P.PROVIDERID = PR.VAL
     JOIN @InTemp T ON P.val=T.KEYY
     -- 修正NOT IN逻辑：仅排除当前provider下被禁用的val
     WHERE P.val NOT IN 
     (SELECT VAL FROM PROVIDERBREAK PB WHERE PB.PROVIDERID = P.PROVIDERID) 
      
)X WHERE X.XCOL=(SELECT COUNT(*)FROM @InTemp)

执行后返回结果完全符合预期：

ProviderId   VAL
3            10
3            11
3            12
3            13

• 修正后的NOT IN子查询增加了providerid匹配，只会排除对应provider被禁用的val，不会全局误判其他provider的同值记录
• 保留原有窗口函数计数逻辑，确保返回的provider必须覆盖输入的全部目标val值
• 补充了和providers表的关联，满足从主表查询的需求

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