问如何从一个与Server中的另一个表完全匹配的表中选择所有记录？

EN

-- tables declaration ---------------------------------------------------------------------
declare @one as table (serviceid int null)
declare @two as table (id int null, serviceid int null)
-- values insert --------------------------------------------------------------------------
insert into @two values (15,195),(15,84),(16,195),(16,84),(16,NULL),(17,195),(17,84),(17,8)
insert into @one values (195),(84)
-- actual query ---------------------------------------------------------------------------
select id from (
select id,
       --check, where we have null records in @one caused by null values in @two, which is acceptable (assifgned value when acceptable = 0)
       case when ([ONE].serviceid is not null and [TWO].serviceid is not null) or ([ONE].serviceid is null and [TWO].serviceid is null) then 0 else 1 end [IsMatched]
from (
    select *, COUNT(*) over (partition by id) as [idCount] from @two
) [TWO] left join (
    select serviceid, COUNT(*) over (partition by (select null)) [idCount] from @one
) [ONE] on ([TWO].idCount = [ONE].idCount and [TWO].serviceid = [ONE].serviceid)
) [a] group by id
having SUM(IsMatched) = 0

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);
--insert into @one values (195),(84);
Insert into @one values (195),(84),(8)

select distinct t.id
from @two t
where exists (select * from @one o where t.serviceid = o.serviceid)
      and (select count(*) from @one) = (select count(*) from @two t1 where t1.id = t.id);