如何用SQL按队友ID/名称将团队信息合并至单行？

不同SQL数据库有各自简洁的聚合函数，可实现将同一分组下的多行值合并为单行字符串，以下是主流数据库的具体实现：

1. MySQL/MariaDB：GROUP_CONCAT()函数 #

这是最直接的实现方式，通过该聚合函数在分组后合并指定列内容：

SELECT
    tm.teammate_id AS "Teammate ID",
    tm.name AS "Teammate name",
    GROUP_CONCAT(DISTINCT itt.team_id ORDER BY itt.team_id SEPARATOR ', ') AS "Team IDs",
    GROUP_CONCAT(DISTINCT it.team ORDER BY it.team SEPARATOR ', ') AS "Teams"
FROM
    intercom_teammates AS tm
LEFT JOIN intercom_teammate_teams AS itt 
    ON tm.teammate_id = itt.teammate_id
LEFT JOIN intercom_teams AS it 
    ON itt.team_id = it.team_id  -- 修正原查询的关联逻辑错误
GROUP BY
    tm.teammate_id, tm.name;

• DISTINCT：避免同一队友重复关联同一团队时出现重复值
• ORDER BY：让合并后的内容按指定顺序排列
• SEPARATOR：自定义分隔符，默认值为逗号

2. PostgreSQL：STRING_AGG()函数 #

PostgreSQL使用该聚合函数实现相同需求：

SELECT
    tm.teammate_id AS "Teammate ID",
    tm.name AS "Teammate name",
    STRING_AGG(DISTINCT itt.team_id::TEXT, ', ' ORDER BY itt.team_id) AS "Team IDs",
    STRING_AGG(DISTINCT it.team, ', ' ORDER BY it.team) AS "Teams"
FROM
    intercom_teammates AS tm
LEFT JOIN intercom_teammate_teams AS itt 
    ON tm.teammate_id = itt.teammate_id
LEFT JOIN intercom_teams AS it 
    ON itt.team_id = it.team_id
GROUP BY
    tm.teammate_id, tm.name;

• 若team_id为数值类型，需用::TEXT转换为字符串后再合并

3. SQL Server 2017+：STRING_AGG()函数 #

语法简洁，支持直接指定排序规则：

SELECT
    tm.teammate_id AS "Teammate ID",
    tm.name AS "Teammate name",
    STRING_AGG(DISTINCT itt.team_id, ', ') WITHIN GROUP (ORDER BY itt.team_id) AS "Team IDs",
    STRING_AGG(DISTINCT it.team, ', ') WITHIN GROUP (ORDER BY it.team) AS "Teams"
FROM
    intercom_teammates AS tm
LEFT JOIN intercom_teammate_teams AS itt 
    ON tm.teammate_id = itt.teammate_id
LEFT JOIN intercom_teams AS it 
    ON itt.team_id = it.team_id
GROUP BY
    tm.teammate_id, tm.name;

• WITHIN GROUP (ORDER BY ...)：直接定义合并内容的排序逻辑

4. Oracle：LISTAGG()函数 #

Oracle通过该函数实现多行值合并：

SELECT
    tm.teammate_id AS "Teammate ID",
    tm.name AS "Teammate name",
    LISTAGG(DISTINCT itt.team_id, ', ') WITHIN GROUP (ORDER BY itt.team_id) AS "Team IDs",
    LISTAGG(DISTINCT it.team, ', ') WITHIN GROUP (ORDER BY it.team) AS "Teams"
FROM
    intercom_teammates AS tm
LEFT JOIN intercom_teammate_teams AS itt 
    ON tm.teammate_id = itt.teammate_id
LEFT JOIN intercom_teams AS it 
    ON itt.team_id = it.team_id
GROUP BY
    tm.teammate_id, tm.name;

原查询的问题说明 #

你提供的原查询中，最后一个LEFT JOIN的关联条件错误，误写为tm.teammate_id = itt.teammate_id，正确逻辑应该是通过团队ID关联：itt.team_id = it.team_id，这是导致你之前无法得到正确结果的原因之一。

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