如何编写查询获取关系中唯一元素及仅含customer角色的用户

咱们一个个来解决你的问题哈：

1. 如何从关系表中获取唯一元素？ #

要获取关系表中的唯一元素，最常用的有两种方式，按需选择即可：

• 使用DISTINCT关键字：直接对查询结果去重，适合单纯获取唯一值列表的场景。比如从users表中提取所有不重复的邮箱地址：

  SELECT DISTINCT email FROM users;
  

• 使用GROUP BY子句：按指定字段分组，既可以用来获取唯一值，还能配合聚合函数做统计。比如同样获取唯一邮箱，同时统计每个邮箱的注册次数：

  SELECT email, COUNT(*) AS register_count FROM users GROUP BY email;
  

  要是只需要唯一值，单纯用SELECT email FROM users GROUP BY email;也能实现。

2. 查询仅拥有"customer"角色的用户 #

你的原查询存在逻辑漏洞：它只是排除了几个特定角色，但只要用户关联了customer角色（哪怕同时还有其他角色），这条customer的关联记录依然会被匹配到，所以不符合“仅拥有一个角色且是customer”的要求。这里给你两种靠谱的实现方式：

方法一：先选目标用户，再排除有其他角色的人 #

先筛选出所有拥有customer角色的用户，再去掉那些还关联了其他角色的用户：

SELECT u.*
FROM users u
INNER JOIN roles_users ru ON u.id = ru.user_id
INNER JOIN roles r ON ru.role_id = r.id
WHERE r.name = 'customer'
AND u.id NOT IN (
    -- 子查询找出所有拥有非customer角色的用户ID
    SELECT ru2.user_id
    FROM roles_users ru2
    INNER JOIN roles r2 ON ru2.role_id = r2.id
    WHERE r2.name != 'customer'
);

方法二：分组统计角色数量和类型 #

通过GROUP BY按用户分组，确保每个用户的角色数量只有1个，且这个角色恰好是customer：

SELECT u.*
FROM users u
INNER JOIN roles_users ru ON u.id = ru.user_id
INNER JOIN roles r ON ru.role_id = r.id
GROUP BY u.id
-- 限制用户只有1种角色，且该角色是customer
HAVING COUNT(DISTINCT r.name) = 1 AND MAX(r.name) = 'customer';

这里用MAX(r.name)是因为如果用户只有一种角色，最大值就是该角色的名称，换成MIN也能达到同样效果。

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