过滤出字符在与您提供的列表匹配的CharacterToName表中出现的次数(我假设您可以将其转换为表变量或临时表)。

declare @Character table (id int, [name] varchar(12));

insert into @Character (id, [name])
values
(1, 'tom'),
(2, 'jerry'),
(3, 'dog');

declare @NameToCharacter table (id int, nameId int, characterId int);

insert into @NameToCharacter (id, nameId, characterId)
values
(1, 1, 1),
(2, 1, 3),
(3, 1, 2),
(4, 2, 1);

declare @RequiredNames table (nameId int);

insert into @RequiredNames (nameId)
values
(1),
(2);

select *
from @Character C
where (
    select count(*)
    from @NameToCharacter NC
    where NC.characterId = c.id
    and NC.nameId in (select nameId from @RequiredNames)
) = 2;

返回：

Id|名称

1|Tom

注意：提供这里所示的DDL+DML使人们更容易帮助您。

这是经典的Relational Division加上余数。**

有许多不同的解决方案。@Dalek给了你一个很好的答案：内部连接所有东西，然后检查每一盘都有合适的数量。这通常是最快的解决方案。

如果希望确保它可以处理动态数量的行数，只需将最后一行更改为

) = (SELECT COUNT(*) FROM @RequiredNames);

还有另外两种常见的解决方案。

• LEFT-JOIN并检查是否已连接所有行

SELECT *
FROM @Character c
WHERE EXISTS (SELECT 1
    FROM @RequiredNames rn
    LEFT JOIN @NameToCharacter nc ON nc.nameId = rn.nameId AND nc.characterId = c.id
    HAVING COUNT(*) = COUNT(nc.nameId)  -- all rows are joined
);

• 双重反联接，也就是说：没有“不在集合中”的“必需的”

SELECT *
FROM @Character c
WHERE NOT EXISTS (SELECT 1
    FROM @RequiredNames rn
    WHERE NOT EXISTS (SELECT 1
        FROM @NameToCharacter nc
        WHERE nc.nameId = rn.nameId AND nc.characterId = c.id
    )
);

另一个答案的变体使用窗口聚合而不是子查询。我不认为这是性能上的，但它在某些情况下可能有用处。

SELECT *
FROM @Character c
WHERE EXISTS (SELECT 1
    FROM (
      SELECT *, COUNT(*) OVER () AS cnt
      FROM @RequiredNames
    ) rn
    JOIN @NameToCharacter nc ON nc.nameId = rn.nameId AND nc.characterId = c.id
    HAVING COUNT(*) = MIN(rn.cnt)
);

db<>fiddle