SQL查询仅拥有指定角色且无其他角色的用户实现方法

原有写法的缺陷 #

通过WHERE R.RoleName IN (...)匹配角色的写法仅做行级过滤：只要用户任意一条角色关联记录命中指定列表，就会被纳入结果集，完全没有校验用户是否存在列表外的其他角色，因此无法排除持有额外角色的用户。

实现思路 #

要精准匹配「角色范围完全限定在指定集合内、无额外角色」的用户，采用分组聚合的方案兼容性最强，逻辑也最易维护，根据需求可覆盖两种判断维度：

• 基础校验：用户关联的所有角色全部落在指定角色列表内，不存在列表外的角色
• 全量持有校验（按需添加）：如果要求用户必须同时持有列表内的所有指定角色，需额外校验用户持有的指定角色去重计数，和传入的指定角色总数相等

适配当前场景的SQL代码 #

基于给出的表结构与测试数据，查询仅同时持有SysAdmin、Manager两个角色、无其他角色的用户，代码如下：

SELECT U.UserName
FROM User U
INNER JOIN UserRole UR 
    ON U.UserID = UR.UserID
INNER JOIN Role R 
    ON UR.RoleID = R.RoleID
GROUP BY U.UserID, U.UserName
HAVING 
    -- 排除存在指定列表外角色的用户
    SUM(CASE WHEN R.RoleName NOT IN ('SysAdmin', 'Manager') THEN 1 ELSE 0 END) = 0
    -- 确保用户同时持有全部2个指定角色，缺任一角色都不返回
    AND COUNT(DISTINCT CASE WHEN R.RoleName IN ('SysAdmin', 'Manager') THEN R.RoleID END) = 2;

结果校验 #

对照提供的示例数据运行代码：

• 用户Joe关联了Admin、SysAdmin、Manager三个角色，存在列表外的Admin角色，直接被过滤，不会出现在结果中
• 用户Bob仅关联SysAdmin、Manager两个角色，两个校验条件均满足，会被正确返回

灵活适配调整 #

如果需求变更为「用户角色不能超出指定列表范围，但不需要持有列表内全部角色」（例如仅持有SysAdmin的用户也符合要求），仅需删除HAVING子句中第二个计数校验条件即可。

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