SQL查询求助：如何筛选仅拥有role_id=2角色的用户

嘿，这个问题我太熟悉了！只加WHERE role_id=2确实会把那些同时拥有其他角色的用户也包含进来，根本达不到“仅拥有role_id=2”的要求。给你几个实用的SQL方案，你可以根据自己的数据库类型和数据规模选择：

方法1：GROUP BY + HAVING（直观易懂） #

核心思路是按user_id分组，然后确保该用户的所有角色只有role_id=2：

写法A：通过统计唯一角色数+验证角色值 #

SELECT user_id
FROM your_table_name  -- 替换成你的表名
GROUP BY user_id
HAVING COUNT(DISTINCT role_id) = 1  -- 确保用户只有1种角色
   AND MAX(role_id) = 2;            -- 确保这个角色是2

写法B：直接统计非目标角色的数量 #

这种写法更直白，直接检查用户有没有非2的角色：

SELECT user_id
FROM your_table_name
GROUP BY user_id
HAVING SUM(CASE WHEN role_id != 2 THEN 1 ELSE 0 END) = 0;

方法2：子查询排除法（性能友好） #

先找出所有拥有非2角色的用户ID，然后在主查询里排除这些用户，同时确保用户确实拥有role_id=2：

用NOT IN（注意NULL情况） #

SELECT DISTINCT user_id
FROM your_table_name
WHERE role_id = 2
  AND user_id NOT IN (
    SELECT user_id
    FROM your_table_name
    WHERE role_id != 2
  );

注意：如果user_id可能存在NULL值，NOT IN会导致结果异常，这时候推荐用下面的NOT EXISTS。

用NOT EXISTS（性能更优） #

NOT EXISTS在多数数据库中性能更好，因为它找到匹配项就会停止搜索：

SELECT DISTINCT t1.user_id
FROM your_table_name t1
WHERE t1.role_id = 2
  AND NOT EXISTS (
    SELECT 1
    FROM your_table_name t2
    WHERE t2.user_id = t1.user_id
      AND t2.role_id != 2
  );

方法3：LEFT JOIN筛选法（逻辑清晰） #

通过左连接关联用户的非2角色记录，然后筛选出没有匹配到非2角色的用户：

SELECT DISTINCT t1.user_id
FROM your_table_name t1
LEFT JOIN your_table_name t2
  ON t1.user_id = t2.user_id
  AND t2.role_id != 2
WHERE t1.role_id = 2
  AND t2.user_id IS NULL;  -- 说明该用户没有非2的角色

小建议 #

• 如果数据量不大，方法1的GROUP BY写法最直观，容易理解和维护；
• 如果数据量较大，方法2的NOT EXISTS或方法3的LEFT JOIN通常性能更优，建议优先选用。

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