求助：实现查询本人所属群组及组成员的SQL方案

嘿，我来帮你搞定这个SQL查询问题～你想要的是找出自己所属的所有群组，并且每个群组后面跟着它的所有成员列表对吧？用UNION确实不对——因为UNION是用来合并多个查询的结果行，而我们需要的是按群组聚合成员，把同一个群组的所有成员合并成一个字符串，和群组名称放在同一行展示。

核心思路拆解 #

1. 锁定自己的群组：从groups_members表筛选出属于你的所有groupid，这一步是为了过滤出你只关心的群组。
2. 关联群组名称：把筛选出的groupid和groups表关联，拿到每个群组的名称。
3. 聚合成员列表：再次关联groups_members表获取这些群组的所有成员，用数据库的字符串聚合函数把成员合并成一个空格分隔的字符串。

分数据库的SQL实现 #

下面针对主流数据库给出具体代码，记得把示例中的1替换成你实际的userid：

MySQL/MariaDB 版本

SELECT 
    g.name AS group_name,
    GROUP_CONCAT(gm.userid SEPARATOR ' ') AS members
FROM 
    groups g
-- 第一步：关联自己的群组记录，筛选出你所属的群组
JOIN 
    groups_members gm_me ON g.id = gm_me.groupid
-- 第二步：关联该群组的所有成员
JOIN 
    groups_members gm ON g.id = gm.groupid
WHERE 
    gm_me.userid = 1 -- 替换成你的userid
GROUP BY 
    g.id, g.name;

PostgreSQL 版本

SELECT 
    g.name AS group_name,
    STRING_AGG(gm.userid::TEXT, ' ') AS members
FROM 
    groups g
JOIN 
    groups_members gm_me ON g.id = gm_me.groupid
JOIN 
    groups_members gm ON g.id = gm.groupid
WHERE 
    gm_me.userid = 1
GROUP BY 
    g.id, g.name;

SQL Server 版本

SELECT 
    g.name AS group_name,
    -- 用STUFF+FOR XML PATH实现字符串聚合
    STUFF((
        SELECT ' ' + CAST(gm_inner.userid AS VARCHAR(MAX))
        FROM groups_members gm_inner
        WHERE gm_inner.groupid = g.id
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS members
FROM 
    groups g
JOIN 
    groups_members gm_me ON g.id = gm_me.groupid
WHERE 
    gm_me.userid = 1
GROUP BY 
    g.id, g.name;

额外优化：显示成员名称而非ID #

如果你的系统里有users表（存储用户ID和名称），想要展示成员名称而不是userid，只需要加入users表的关联即可，以MySQL为例：

SELECT 
    g.name AS group_name,
    GROUP_CONCAT(u.name SEPARATOR ' ') AS members
FROM 
    groups g
JOIN 
    groups_members gm_me ON g.id = gm_me.groupid
JOIN 
    groups_members gm ON g.id = gm.groupid
JOIN 
    users u ON gm.userid = u.id -- 关联用户表拿名称
WHERE 
    gm_me.userid = 1
GROUP BY 
    g.id, g.name;

为什么UNION不适用？ #

UNION的作用是把多个查询的结果集按行合并，比如你如果尝试用UNION查询群组名称和成员ID，会得到类似：

Group 1
Member 1
Member 3
...
Group 4
Member 1
...

这种分行的结果，完全不符合你想要的“群组名称 + 对应成员列表”在同一行的预期，所以用聚合函数才是正确的方向。

内容的提问来源于stack exchange，提问作者Théo SALA