SQL三表关联查询：如何合并展示参与者及其所属多个分组

原有SQL的问题 #

• 关联条件错误：participant_groups表的关联字段应为participant_id和group_id，原有语句误用了id字段，会导致关联结果完全不符合预期
• 不需要使用CROSS JOIN，会生成不必要的笛卡尔积
• 没有做分组聚合，导致归属多个分组的参与者生成重复记录

修正方案 #

核心逻辑是先按参与者维度分组，再通过字符串聚合函数将同一参与者的多个分组名拼接为单个字段，不同数据库的聚合函数略有差异：

MySQL 写法 #

SELECT 
    p.id AS participant_id,
    p.name AS participant_name,
    GROUP_CONCAT(DISTINCT g.name ORDER BY g.name SEPARATOR ', ') AS all_groups
FROM participants p
LEFT JOIN participant_groups pg ON p.id = pg.participant_id
LEFT JOIN groups g ON pg.group_id = g.id
GROUP BY p.id, p.name
ORDER BY p.name, p.id;

PostgreSQL / SQL Server 写法 #

SELECT 
    p.id AS participant_id,
    p.name AS participant_name,
    STRING_AGG(DISTINCT g.name, ', ' ORDER BY g.name) AS all_groups
FROM participants p
LEFT JOIN participant_groups pg ON p.id = pg.participant_id
LEFT JOIN groups g ON pg.group_id = g.id
GROUP BY p.id, p.name
ORDER BY p.name, p.id;

Oracle 写法 #

SELECT 
    p.id AS participant_id,
    p.name AS participant_name,
    LISTAGG(g.name, ', ') WITHIN GROUP (ORDER BY g.name) AS all_groups
FROM participants p
LEFT JOIN participant_groups pg ON p.id = pg.participant_id
LEFT JOIN groups g ON pg.group_id = g.id
GROUP BY p.id, p.name
ORDER BY p.name, p.id;

说明 #

• 采用LEFT JOIN而非INNER JOIN，可保留没有归属任何分组的参与者，其all_groups字段值为NULL，可通过COALESCE/IFNULL函数调整为自定义默认值
• 聚合函数中的DISTINCT可避免关联脏数据导致的重复分组名，业务无重复关联场景可删除
• 可自行修改拼接分隔符，比如将, 替换为、或;适配展示需求

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