SQL Server同表匹配记录查询：基于角色权限的组数据关联需求

需求回顾 #

现有表Tab1的结构与数据如下：

Id      Name            Group   Role
=====================================
1   ADMIN_GROUP        2    501
1   ADMIN_GROUP        2    502
1   ADMIN_GROUP        2    503
1   ADMIN_GROUP        2    504
1   ADMIN_GROUP        2    1001

3   OtherGroup         2    501
3   OtherGroup         2    502
3   OtherGroup         2    503
3   OtherGroup         2    1001

需要实现的查询逻辑：

• 选择OtherGroup（Id=3）时，返回ADMIN_GROUP和OtherGroup（ADMIN_GROUP拥有OtherGroup的全部角色）
• 选择ADMIN_GROUP（Id=1）时，仅返回ADMIN_GROUP（OtherGroup缺少角色504，不满足条件）

原查询使用IN子句仅能判断组与目标组存在角色交集，无法保证包含目标组的全部角色，因此不符合需求。

正确查询语句 #

方式一：基于角色集合完全包含判断 #

SELECT DISTINCT t1.Id, t1.Name
FROM Tab1 t1
WHERE NOT EXISTS (
    -- 找出目标组有但当前组没有的角色
    SELECT t2.Role
    FROM Tab1 t2
    WHERE t2.Id = 3 -- 此处替换为目标组的Id
    EXCEPT
    SELECT t3.Role
    FROM Tab1 t3
    WHERE t3.Id = t1.Id
)

方式二：基于角色数量匹配判断 #

SELECT t1.Id, t1.Name
FROM Tab1 t1
JOIN Tab1 t2 ON t1.Role = t2.Role AND t2.Id = 3 -- 此处替换为目标组的Id
GROUP BY t1.Id, t1.Name
HAVING COUNT(DISTINCT t1.Role) = (
    -- 获取目标组的总角色数量
    SELECT COUNT(DISTINCT Role) FROM Tab1 WHERE Id = 3
)

逻辑说明 #

• 方式一：通过EXCEPT对比目标组与当前组的角色集合，NOT EXISTS确保不存在「目标组有但当前组没有」的角色，即当前组完全包含目标组的所有角色。
• 方式二：先关联当前组与目标组的共同角色，统计当前组匹配到的角色数量，若该数量等于目标组的总角色数，则说明当前组包含目标组的全部角色。

将语句中的目标组Id替换为1时，OtherGroup因缺少角色504会被排除，仅返回ADMIN_GROUP；替换为3时，ADMIN_GROUP和OtherGroup都会被返回。

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