MySQL内连接多条件查询问题：筛选满足指定角色组合的用户

原SQL的核心问题是WHERE子句条件矛盾——r.role_id=1 AND r.role_id BETWEEN 2 AND 7要求同一行的role_id既要等于1，又要落在2-7区间，这显然不可能成立，导致逻辑完全错误。以下是几种可行的修正方案：

方案一：分组+HAVING统计（推荐） #

先筛选出角色为1或2-4的记录，分组后通过条件统计验证两个角色要求都满足：

SELECT a.*
FROM `user_info` a 
INNER JOIN `user_role_info` r ON r.`user_id` = a.`user_id`
WHERE r.`role_id` = 1 OR r.`role_id` BETWEEN 2 AND 4
GROUP BY a.`user_id`
HAVING 
    SUM(CASE WHEN r.`role_id` = 1 THEN 1 ELSE 0 END) >= 1
    AND SUM(CASE WHEN r.`role_id` BETWEEN 2 AND 4 THEN 1 ELSE 0 END) >= 1;

该方案逻辑清晰，通过精准的条件统计避免误判，能准确筛选出同时满足两个角色要求的用户。

方案二：使用EXISTS子查询 #

通过两次EXISTS子查询分别验证用户是否拥有目标角色，逻辑直观且性能表现较好：

SELECT *
FROM `user_info` a
WHERE 
    EXISTS (SELECT 1 FROM `user_role_info` r WHERE r.`user_id` = a.`user_id` AND r.`role_id` = 1)
    AND EXISTS (SELECT 1 FROM `user_role_info` r WHERE r.`user_id` = a.`user_id` AND r.`role_id` BETWEEN 2 AND 4);

如果user_role_info表有user_id+role_id的联合索引，该方案能快速定位符合条件的记录。

方案三：两次JOIN关联角色表 #

通过两次内连接分别匹配不同角色的记录，需注意去重避免重复结果：

SELECT DISTINCT a.*
FROM `user_info` a
INNER JOIN `user_role_info` r1 ON r1.`user_id` = a.`user_id` AND r1.`role_id` = 1
INNER JOIN `user_role_info` r2 ON r2.`user_id` = a.`user_id` AND r2.`role_id` BETWEEN 2 AND 4;

DISTINCT用于排除用户拥有多个2-4角色时产生的重复行。

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