如何筛选未在至少一个输入组或非输入组使用的实体？

表结构与数据示例 #

现有三张数据库表：

• Entity(EntityId int)：存储实体ID
• Group(GroupId int)：存储分组ID
• Mapping(MapId int, GroupId int, EntityId int)：存储实体与分组的关联关系

具体数据如下：

-- Entity表数据
EntityId
1
2
3
4

-- Group表数据
GroupId
101
102
103

-- Mapping表数据
MapId GroupId EntityId
1       101      2
2       102      1
3       102      3
4       103      3

筛选需求 #

需要筛选满足未关联所有输入分组的实体，即实体至少未关联一个指定的输入分组。不同输入分组对应的输出示例：

Input-GroupId  Output-Entities
101            1,3,4 (实体2关联了101，不符合条件被排除)
101,102        1,2,3,4 (实体1仅关联102、实体2仅关联101、实体3仅关联102、实体4未关联任何输入分组，均符合条件)
102,103        1,2,4 (实体3同时关联了102和103，不符合条件被排除)

补充说明：输入两个分组时，排除同时关联两个分组的实体（维恩图的交集区域），其余实体均需保留。

尝试的SQL及问题 #

我尝试了以下SQL语句，它在输入分组为(101)、(101,102)时可正常运行，但在输入分组为(102,103)时无法得到正确结果：

SELECT distinct EntityId from Entity e
JOIN Mapping map ON e.EntityId != map.EntityId 
JOIN #temp t ON map.GroupId = t.GroupId  order by EntityId

问题分析与正确SQL #

原SQL通过e.EntityId != map.EntityId关联表，会产生大量无效匹配，无法准确统计实体与输入分组的关联情况。正确思路是统计每个实体关联的输入分组数量，再与输入分组的总数量对比，筛选出关联数量小于总数量的实体。

假设输入分组存储在临时表#temp(GroupId int)中，正确SQL如下：

-- 统计输入分组的总数
DECLARE @InputGroupCount INT = (SELECT COUNT(*) FROM #temp);

SELECT e.EntityId
FROM Entity e
LEFT JOIN (
    -- 统计每个实体关联的输入分组数量
    SELECT EntityId, COUNT(DISTINCT GroupId) AS InputGroupCount
    FROM Mapping
    WHERE GroupId IN (SELECT GroupId FROM #temp)
    GROUP BY EntityId
) input_map ON e.EntityId = input_map.EntityId
-- 筛选条件：实体关联的输入分组数量 < 输入分组总数（包括未关联任何输入分组的实体）
WHERE ISNULL(input_map.InputGroupCount, 0) < @InputGroupCount
ORDER BY e.EntityId;

逻辑说明 #

1. 先统计输入分组的总数量@InputGroupCount；
2. 子查询input_map计算每个实体实际关联了多少个输入分组；
3. 通过LEFT JOIN保留所有实体，用ISNULL处理未关联任何输入分组的实体（关联数量视为0）；
4. 最终筛选出关联数量小于输入分组总数的实体，即至少未关联一个输入分组的实体。

测试该SQL可完全匹配所有示例的输出结果。

内容的提问来源于stack exchange，提问作者Unbalanced-Tree