如何查询数据库中仅拥有User角色、无其他关联角色的Account记录

前提约定 #

默认三张表的核心字段如下，实际使用时替换为你的业务字段名即可：

• Account表：主键为account_id，存储账户的基础信息
• Role表：主键为role_id，角色名称存储字段为role_name
• Account_Role关联中间表：通过account_id关联Account表，role_id关联Role表，维护账户和角色的多对多关系

方案1：分组聚合判断（通用推荐，适配多角色场景） #

按账户维度分组后，直接判断两个规则：该账户总角色数为1，且唯一的角色是User，即可精准过滤出仅持有User角色的账户，自动排除同时持有Admin或其他任意角色的账户。

SELECT a.*
FROM Account a
INNER JOIN Account_Role ar 
  ON a.account_id = ar.account_id
INNER JOIN Role r 
  ON ar.role_id = r.role_id
GROUP BY a.account_id
HAVING COUNT(DISTINCT r.role_id) = 1
   AND SUM(CASE WHEN r.role_name = 'User' THEN 1 ELSE 0 END) = 1;

该写法兼容MySQL、PostgreSQL、SQL Server等所有主流关系型数据库。

方案2：排除法（仅适用于系统只有Admin、User两种角色的场景） #

如果你的业务中角色只有Admin和User两类，可以用排除法获得更好的查询性能：先过滤掉所有持有Admin角色的账户，再从剩余账户中筛选持有User角色的即可。

SELECT DISTINCT a.*
FROM Account a
INNER JOIN Account_Role ar 
  ON a.account_id = ar.account_id
INNER JOIN Role r 
  ON ar.role_id = r.role_id
WHERE r.role_name = 'User'
  AND a.account_id NOT IN (
      SELECT ar2.account_id
      FROM Account_Role ar2
      INNER JOIN Role r2 
        ON ar2.role_id = r2.role_id
      WHERE r2.role_name = 'Admin'
  );

内容的提问来源于stack exchange，提问作者Đức Long