mysql - 在单个 mysql 查询中获取组中的用户(仅允许)

Question

从下表中，我必须根据以下逻辑获取指定组中允许的用户列表。

• 情况1:没有匹配项时所有用户都符合资格(没有记录)用户组表)。
• 情况 2:组中匹配的用户(具有排除 = 0) 在 user_group 表中
• 情况 3:组中的用户不是被拒绝(排除=1)

我的用户

user_id | user_name 
  1     |  a
  2     |  b
  3     |  c

我的群组

group_id | group_code
  1      |  G1
  2      |  G2
  3      |  G3

用户组

userid  | groupid | exclusion
  1     |   1     |  0
  1     |   2     |  0
  2     |   1     |  1 

以下查询适用于情况 1 和 3，但不适用于情况 2。

SELECT u.id, g.id, g.code, ug.exclusion FROM MyUser u
join MyGroup g 
LEFT JOIN user_group ug ON ug.group_id = g.id 
     AND ug.user_id = 1 -- works for user 2 and 3 but fails for user 1         
WHERE (ug.exclusion <> 0 OR ug.exclusion IS NULL) 

因此，在排除 = 1 的记录的情况下，则该记录将被获取，并且其他组将被添加为空条件。问题是 null 字段也会被添加以排除 = 0，这根本不是必需的。

请建议我们是否可以仅使用单个查询来获取给定组的用户列表

预期结果:

g1(a,c), g2(a,b,c), g3(b,c) 

Answer 1

这应该有效，或者接近它。

select u.user_id, g.group_id, g.code
from MyGroup g
    cross join users u 
    left join user_group allowed ON u.user_id = allowed.user_id AND g.group_id = allowed.group_id AND allowed.exclusion = 0
    left join user_group allowbydefault ON u.user_id = allowbydefault.user_id
where (allowed.user_id is not null OR allowbydefault.group_id is null)
-- and g.group_id = 1 -- uncomment to get everyone authorized for group #1
-- and u.user_id = 35 -- uncomment to get every group that user #35 is authorized for
group by u.user_id, g.group_id, g.code

我使用 SQL Server 来测试这一点，但这至少应该为 MySQL 提供另一个攻击点。