基于多Lookup表的查询：筛选关联多个动态nameId的字符

核心思路 #

要筛选出同时关联所有目标nameId的角色，核心逻辑是统计每个角色关联的目标nameId数量，确保该数量等于目标列表的总个数，以此证明角色关联了所有指定的name。

实现步骤（无需动态SQL） #

1. 存储动态nameId列表

由于需要匹配的nameId是动态的，我们可以用临时表或表值参数来存储目标列表，避免动态SQL：

-- 示例：用临时表存储目标nameId（实际可动态插入任意数量值）
CREATE TABLE #TargetNameIds (nameId INT);
INSERT INTO #TargetNameIds VALUES (1), (2);

2. 执行筛选查询

通过关联目标列表、分组统计，筛选出符合条件的角色：

SELECT c.id, c.name
FROM NameToCharacterTable ntc
-- 关联目标nameId列表，仅保留相关记录
JOIN #TargetNameIds tni ON ntc.nameId = tni.nameId
-- 关联角色表获取名称
JOIN CharacterTable c ON ntc.characterId = c.id
-- 按角色分组
GROUP BY c.id, c.name
-- 筛选条件：角色关联的目标nameId数量等于列表总个数
HAVING COUNT(DISTINCT ntc.nameId) = (SELECT COUNT(*) FROM #TargetNameIds);

更灵活的表值参数方案（SQL Server适用） #

如果是在存储过程或频繁调用的场景中，使用表值参数传递动态列表更高效：

-- 先定义表值类型（只需创建一次）
CREATE TYPE NameIdList AS TABLE (nameId INT);

-- 使用时声明并填充参数
DECLARE @TargetIds NameIdList;
INSERT INTO @TargetIds VALUES (1), (2);

-- 执行查询
SELECT c.id, c.name
FROM NameToCharacterTable ntc
JOIN @TargetIds tni ON ntc.nameId = tni.nameId
JOIN CharacterTable c ON ntc.characterId = c.id
GROUP BY c.id, c.name
HAVING COUNT(DISTINCT ntc.nameId) = (SELECT COUNT(*) FROM @TargetIds);

结果验证 #

基于你提供的测试数据，以上查询会返回：

id | name
---------
1  | tom

完全符合你需要的“仅返回同时关联nameId 1和2的角色”的需求。

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