DISTINCT或GROUP BY与NOT IN一起使用会返回错误的结果

Question

我有两张桌子

具有列的用户: uid、名称、邮件等

包含列的users_roles : uid、rid

在users_roles中，每次向用户添加角色时，都会在users_roles表中列出该角色。因此，假设用户1具有角色1和4。在该表中：

users_roles:  
uid | rid  
 1  |  1  
 1  |  4  

我需要返回所有没有角色4或5的用户。我已经尝试将Group By和Distinct与NOT IN结合使用。我经常遇到的问题是，如果用户同时具有角色1和角色4，则它们将在结果中返回。下面是my Group By查询的一个示例：

SELECT *
FROM users AS u
LEFT JOIN users_roles AS ur ON u.uid = ur.uid
WHERE ur.rid NOT
IN ( 4, 5 )
GROUP BY ur.uid

我也尝试了子查询，但都没有用，因为问题似乎是Group By在完成查询后合并了行。因此，它只是找到包含uid1RID4的记录，并在结果中返回它。

我不能使用的Drupal模块视图(由于视图批量操作的安全问题)通过执行以下操作实现了预期的结果：

LEFT JOIN users_roles ON users.uid = users_roles.uid 
AND (users_roles.rid = '4' OR users_roles.rid = '5')

对于长期维护，我不希望每次添加角色时都要更新代码，这将导致一个很长的查询。

我看了以下内容：

Aggregating Rows

Filtering distinct rows in SQL

虽然有一些Drupal函数可以让我获得角色in列表，我可以在其中取消设置我不希望在结果数组中显示的角色，但我觉得我缺少对SQL的基本理解。在SQL中有没有更好的方法来做到这一点？

Answer 1

我需要返回所有没有角色4和5的用户

select  *
from    users u
where   not exists
        (
        select  *
        from    users_roles ur
        where   ur.rid in (4,5)
                and ur.uid = u.uid
        )

Answer 2

如果您想检查4和5是否都不存在(并且不一定是其中之一)，您可以使用

select  * 
from    users u 
where   not exists 
        ( 
        select  uid
        from    users_roles ur 
        where   ur.rid in (4,5)     
                and ur.uid = u.uid 
    group by uid having count(distinct rid)=2
        ) 

如果列表很长，可以使用包含所有可能值的映射表，并在上面的查询中使用该映射表

Answer 3

我想这样做：

SELECT *
FROM users AS u
LEFT JOIN users_roles AS ur ON (u.uid = ur.uid AND ur.rid IN ( 4, 5 ) )
WHERE ur.rid IS NULL
GROUP BY u.uid