这将为您提供来自 @T 的 ID，这些 ID 已经具有 @NewValues 中提供的值的组合.

This will give you the ID's from @T that already have the combination of values provided in @NewValues.

declare @T table (ID int, Value char(1))
insert into @T values
(1,    'A'),(2,    'B'),(3,    'C'),(3,    'C'),
(4,    'A'),(4,    'D'),(5,    'A'),(5,    'C'),
(5,    'D')

declare @NewValues table(ID int, Value char(1))
insert into @NewValues values (6,    'A'), (6,    'D')

select T.ID
from @T as T
  inner join @NewValues as N
    on T.Value = N.Value
group by T.ID
having count(*) = (select count(*) from @NewValues)

结果:

ID
4
5

如果您只想要完全匹配，这意味着不会返回 ID=5，因为它还有一行包含 Value='C'，您可以改用它.

If you only want exact matches, meaning that ID=5 would not be returned because it also have one row with Value='C' you can use this instead.

select T.ID
from @T as T
  left outer join @NewValues as N
    on T.Value = N.Value
group by T.ID
having count(N.Value) = (select count(*) from @NewValues) and 
       count(*) = (select count(*) from @NewValues)

我看到您的表中有 (3,'C') 和 (3,'C').如果您想使用输入 (6, 'C') 和 (6, 'C') 检测到，则需要此查询.

I see that you have (3,'C') and (3,'C') in your table. If you want to detect that with the input (6, 'C') and (6, 'C') you need this query.

select T.ID
from @T as T
  left outer join (select distinct Value
                   from @NewValues) as N
    on T.Value = N.Value
group by T.ID
having count(N.Value) = (select count(*) from @NewValues) and 
       count(*) = (select count(*) from @NewValues)

填充`@NewValues?具有字符串拆分功能的表.

Fill `@NewValues? table with a string split function.

-- Paramenter to SP
declare @ParamID int = 6
declare @ParamValues varchar(100) = 'A,D'

declare @NewValues table(ID int, Value char(1))
insert into @NewValues
select @ParamID, s
from dbo.Split(',', @ParamValues)