如何用单条SQL查询获取指定用户所属用户组及成员信息？

当然可以用单条查询搞定！而且针对你的表结构，最优方案就是通过多表关联+聚合函数一次性获取id=1用户所在的所有用户组、组内成员名称及组类型，不用折腾多次查询。

针对MySQL的实现方案 #

如果你的数据库是MySQL，我们可以用GROUP_CONCAT函数把成员名称拼接成字符串（后续在应用层很容易转成数组）：

SELECT 
    ug.type,
    GROUP_CONCAT(u.name ORDER BY u.id SEPARATOR ',') AS member_names
FROM 
    user_groups ug
LEFT JOIN 
    users u ON u.id IN (ug.user_1, ug.user_2, ug.user_3)
WHERE 
    1 IN (ug.user_1, ug.user_2, ug.user_3)
GROUP BY 
    ug.type, ug.user_1, ug.user_2, ug.user_3
ORDER BY 
    ug.type;

针对PostgreSQL的实现方案 #

如果用PostgreSQL，直接用ARRAY_AGG就能生成数组格式，更贴合你期望的输出：

SELECT 
    ug.type,
    ARRAY_AGG(u.name ORDER BY u.id) AS member_names
FROM 
    user_groups ug
LEFT JOIN 
    users u ON u.id IN (ug.user_1, ug.user_2, ug.user_3)
WHERE 
    1 IN (ug.user_1, ug.user_2, ug.user_3)
GROUP BY 
    ug.type, ug.user_1, ug.user_2, ug.user_3
ORDER BY 
    ug.type;

代码逻辑说明 #

• 过滤目标组：WHERE 1 IN (ug.user_1, ug.user_2, ug.user_3)确保只查询包含id=1用户的组；
• 关联用户表：通过LEFT JOIN把三个user字段对应的用户名称都关联出来，自动忽略NULL的用户ID；
• 聚合成员名称：用GROUP_CONCAT或ARRAY_AGG把同一个组的成员名称聚合起来，ORDER BY u.id保证成员顺序和示例一致；
• 分组去重：GROUP BY带上ug.user_1, ug.user_2, ug.user_3是为了区分同类型但成员不同的组（比如你示例里两个type=1的组），避免被错误合并。

示例数据执行结果 #

针对你给出的示例数据，执行后会得到：

如果是PostgreSQL，member_names列会直接是数组类型：{"Jeff","Mehmet"}、{"Jeff","Mehmet","Walter"}、{"Jeff","Walter"}，和你期望的输出结构几乎一致。

为什么这是最优方案？ #

• 单条查询完成所有逻辑，避免多次查询数据库带来的网络开销和连接消耗；
• 利用数据库的聚合能力处理数据，减少应用层的代码复杂度；
• 如果给user_groups表的user_1、user_2、user_3字段创建索引，WHERE子句的过滤效率会大幅提升，数据量大的时候优势更明显。

内容的提问来源于stack exchange，提问作者Mehmet Ali