MySQL 仅返回指定组中的用户

Question

在 MySql 数据库中，我有一个相当常见的用户和组，其中有一个 pivot users_groups 以允许 N:M 关系。

表用户

id      | name
--------+----------
1       | Joe
2       | Anna
3       | Max

表组

id       | name
---------+----------
1        | Red
2        | Blue
3        | Green

表 users_groups

id       | userid | groupid
---------+--------+---------
1        | 1      | 2
2        | 3      | 2
3        | 1      | 3 
3        | 2      | 1

所以...红(1)组的成员是安娜(2)，绿(3)组的成员是乔(1)，蓝(2)组的成员是乔(1)和麦克斯(3).

当用户登录时，我有用户 ID(例如 1 代表 Joe)，我想查找我的登录用户也是其中成员的特定组中的所有其他用户。如何获取该组中的用户列表？

我需要使用表单提供的文本查找组名，用户 ID 将从授权/登录代码中获取。如果用户不属于群组，他们应该无法获得群组成员列表。

对于 Red 组，当以 Anna 身份登录时，我应该只能看到一个用户 (Anna)

User  | Group | Users in Group must include the current user
------+------------
1     | Red   | EMPTY

User  | Group | Users in Group must include the current user
------+------------
2     | Red   | Anna

User  | Group | Users in Group must include the current user
------+------------
3     | Red   | EMPTY

对于 Blue 组，如果我以 Joe 或 Max 身份登录，那么我应该会看到一个用户列表(Joe 和 Max)

User  | Group | Users in Group must include the current user
------+------------
1     | Blue  | Joe, Max

User  | Group | Users in Group must include the current user
------+------------
2     | Blue  | EMPTY

User  | Group | Users in Group must include the current user
------+------------
3     | Blue  | Joe, Max

对于 Green 组，当以 Joe 身份登录时，我应该只能看到一个用户 (Joe)

User  | Group | Users in Group must include the current user
------+------------
1     | Green   | Joe

User  | Group | Users in Group must include the current user
------+------------
2     | Green   | EMPTY

User  | Group | Users in Group must include the current user
------+------------
3     | Green   | EMPTY

===更新#1 ===

使用@Erico 的回答和下面的 fiddle 以及更新的表架构以包含已启用和电子邮件字段，我可以通过额外的 enabled 列检查来执行以下操作。但是，我想将所有用户作为结果集中的单独行返回，而不是返回包含所有数据的单个 Users 列。

http://sqlfiddle.com/#!9/80da98/2

SELECT '1' as User, name as Group,
(SELECT GROUP_CONCAT(email) FROM users u, users_groups ug 
 WHERE u.enabled = 1 AND u.id = ug.user_id AND ug.group_id = g.id AND ug.group_id 
 IN (SELECT group_id FROM users_groups WHERE user_id = 1)
) as Users
FROM groups g
 WHERE g.name = 'Blue' AND g.enabled = 1

=== 更新 #2 ===

而不是在一行中返回结果:

User  | Group | Users in Group must include the current user
------+------------
1     | Blue  | Joe, Max

或使用电子邮件代替姓名

User  | Group | Users in Group must include the current user
------+------------
1     | Blue  | joe@mycompany.com, max@hiscompany.com

我想在一行中返回每个用户的完整信息，因此搜索 Group Blue(2) 中的用户 Joe(1) 将返回:

User  | Name  | Email
------+------------
1     | Joe   | joe@mycompany.com
3     | Max   | max@hiscompany.com

Answer 1

这是一个带有子查询的示例，显示的结果如您的问题所示。

Joe 正在显示蓝色组:

SELECT '1' as `User`, name as `Group`, 
(SELECT GROUP_CONCAT(name) FROM users u, users_groups ug 
 WHERE u.id = ug.user_id AND ug.group_id = g.id 
 AND ug.group_id IN (SELECT group_id FROM users_groups WHERE user_id = 1)
 ) as `Users` 
FROM groups g
 WHERE g.name = 'Blue'

http://sqlfiddle.com/#!9/1a735/22/0