SQL Server中如何筛选与指定表完全匹配的记录？含复杂匹配场景

这是个挺考验匹配逻辑细节的SQL问题，我帮你梳理清楚需求并给出对应的实现方案。

首先咱们明确需求核心：要筛选@two中的ID，满足两种匹配规则之一：

• 规则1：该ID下所有非NULL的serviceid都存在于@one中，且非NULL的serviceid数量恰好等于@one的总记录数（完全匹配）
• 规则2：该ID下所有非NULL的serviceid都存在于@one中，同时存在NULL的serviceid（代表“无关匹配”），且非NULL的serviceid数量不超过@one的总记录数

完整SQL代码实现 #

-- 声明表变量并初始化测试数据
declare @one as table (serviceid int null)
declare @two as table (id int null, serviceid int null)
insert into @two values (15,195),(15,84),(16,195),(16,84),(16,NULL),(17,195),(17,84),(17,8)

-- 场景1测试：插入(195),(84)
insert into @one values (195),(84)
-- 场景2测试：取消下面两行注释，替换场景1的插入语句
-- truncate table @one;
-- insert into @one values (195),(84),(8)

-- 获取@one的总记录数
declare @one_count int = (select count(*) from @one)

-- 核心查询逻辑
select t.id
from @two t
left join @one o on t.serviceid = o.serviceid
group by t.id
having 
    -- 确保当前ID下所有非NULL的serviceid都能在@one中找到（无匹配失败项）
    sum(case when t.serviceid is not null and o.serviceid is null then 1 else 0 end) = 0
    and (
        -- 规则1：非NULL serviceid数量与@one记录数完全相等（完全匹配）
        count(t.serviceid) = @one_count
        -- 规则2：存在NULL的serviceid，且非NULL数量不超过@one的记录数（无关匹配）
        or (exists(select 1 from @two t2 where t2.id = t.id and t2.serviceid is null) and count(t.serviceid) <= @one_count)
    )

逻辑拆解 #

咱们逐行解释核心条件的作用：

1. sum(case ...) = 0：这一步是过滤掉那些存在非NULL但不匹配@one的serviceid的ID。如果有任何一个非NULL的serviceid不在@one里，这个求和值就会大于0，直接排除该ID。
2. count(t.serviceid) = @one_count：对应场景1的需求，比如ID=15的非NULL serviceid数量是2，和@one的记录数一致，且所有serviceid都匹配，所以会被选中。
3. exists(...) and count(t.serviceid) <= @one_count：对应场景2中ID=16的情况，它存在NULL的serviceid，且非NULL的匹配数量（2）不超过@one的记录数（3），同时所有非NULL serviceid都在@one里，因此符合要求；而ID=17的非NULL数量刚好等于@one的记录数，会被第一个规则命中，也会被选中。

测试验证 #

• 场景1执行后，查询结果仅返回15，符合预期。
• 场景2执行后，查询结果返回16和17，完全满足需求。

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