多对多映射表SQL查询：筛选仅拥有R1、R2、R3角色的人员

咱们先明确你的核心需求：从多对多的PERSONROLE表中找出只拥有R1、R2、R3这三个角色的用户，但当前的查询会把像P4这种同时持有额外角色（比如R4、R5）的用户也包含进来——这是因为你的查询只验证了用户拥有目标角色的数量，却没限制他们不能存在其他角色。

原查询的问题分析 #

你的SQL语句：

SELECT PERSON FROM RMS.PERSONROLE 
WHERE role IN ('R1', 'R2','R3') 
GROUP BY PERSON 
HAVING COUNT(ROLE)=3;

它的逻辑漏洞在于：只统计了用户在R1/R2/R3范围内的角色数量，完全忽略了用户可能存在的其他角色记录。比如P4在目标角色里有3条数据，但他还有R4、R5的记录，原查询不会过滤掉这类情况。

正确的查询方案 #

这里提供几种可靠的解决思路：

方法1：用NOT EXISTS排除拥有其他角色的用户

SELECT pr.PERSON
FROM RMS.PERSONROLE pr
WHERE pr.role IN ('R1', 'R2', 'R3')
GROUP BY pr.PERSON
HAVING COUNT(DISTINCT pr.role) = 3
AND NOT EXISTS (
    SELECT 1
    FROM RMS.PERSONROLE pr2
    WHERE pr2.PERSON = pr.PERSON
    AND pr2.role NOT IN ('R1', 'R2', 'R3')
);

这个方法先筛选出拥有目标角色的用户并统计数量，再通过子查询排除掉存在非目标角色的用户，逻辑严谨。

方法2：通过总角色数双重验证

如果每个用户的角色记录都是唯一的（没有重复条目），可以用这个更简洁的写法：

SELECT PERSON
FROM RMS.PERSONROLE
GROUP BY PERSON
HAVING COUNT(DISTINCT role) = 3
AND SUM(CASE WHEN role IN ('R1', 'R2', 'R3') THEN 1 ELSE 0 END) = 3;

它的逻辑是：用户的总角色数必须是3，且这3个角色全部属于目标集合。

方法3：用CASE语句直接统计非目标角色数量

SELECT PERSON
FROM RMS.PERSONROLE
GROUP BY PERSON
HAVING COUNT(CASE WHEN role IN ('R1', 'R2', 'R3') THEN 1 END) = 3
AND COUNT(CASE WHEN role NOT IN ('R1', 'R2', 'R3') THEN 1 END) = 0;

这个写法直观易懂：既确保目标角色的数量为3，同时保证非目标角色的数量为0。

最终期望输出 #

执行上述任意一种正确查询后，你会得到符合要求的结果：

Person
------
P1
P5

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