如何用基于集合的TSQL查询判断同ID下表行的完全包含关系

需求说明 #

编写T-SQL查询，判断指定Table2_id下，Table1中的行是否完全包含Table2内同Table2_id的所有记录，要求使用基于集合的操作，避免WHILE循环。

测试案例 #

失败案例：Table1仅包含Table2的部分记录 #

Table1数据

Table2数据

成功案例：Table1完全包含Table2的所有记录 #

Table1数据

Table2数据

成功案例预期结果：返回唯一的Table2_id值 11

解决方案 #

以下是两种高效的基于集合的实现方式：

方法1：通过COUNT分组匹配数对比 #

-- 指定目标Table2_id
DECLARE @TargetTable2Id INT = 11;

SELECT t2.Table2_id
FROM Table2 t2
LEFT JOIN Table1 t1 
  ON t2.Table2_id = t1.Table2_id 
  AND t2.special_id = t1.special_id
WHERE t2.Table2_id = @TargetTable2Id
GROUP BY t2.Table2_id
HAVING COUNT(t2.special_id) = COUNT(t1.special_id);

逻辑：左连接两张表后，按Table2_id分组，对比Table2的总记录数和匹配上Table1的记录数，相等则说明完全包含。

方法2：用EXCEPT检测缺失记录 #

DECLARE @TargetTable2Id INT = 11;

SELECT @TargetTable2Id AS Table2_id
WHERE NOT EXISTS (
    -- 找出Table2有但Table1没有的记录
    SELECT t2.Table2_id, t2.special_id
    FROM Table2 t2
    WHERE t2.Table2_id = @TargetTable2Id
    EXCEPT
    SELECT t1.Table2_id, t1.special_id
    FROM Table1 t1
    WHERE t1.Table2_id = @TargetTable2Id
);

逻辑：如果EXCEPT返回空集，说明Table1没有缺失Table2的任何记录，返回目标ID。

批量判断所有Table2_id #

如果需要一次性检查所有Table2_id的匹配情况，可使用：

SELECT t2.Table2_id
FROM Table2 t2
LEFT JOIN Table1 t1 
  ON t2.Table2_id = t1.Table2_id 
  AND t2.special_id = t1.special_id
GROUP BY t2.Table2_id
HAVING COUNT(t2.special_id) = COUNT(t1.special_id);

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