我有以下表格:
Teammate ID | Teammate name | Team id | Teams |
---|---|---|---|
1 | Amy | 11 | Sales |
1 | Amy | 12 | Support |
1 | Amy | 13 | Marketing |
2 | Peter | 12 | Support |
2 | Peter | 13 | Marketing |
我想按队友ID或队友姓名对我的结果进行分组,以便团队列显示在一行中,如下所示:
Teammate ID | Teammate name | Team id | Teams |
---|---|---|---|
1 | Amy | 11, 12, 13 | Sales, Support, Marketing |
2 | Peter | 12, 13 | Support, Marketing |
哪种功能最适合用于此目的?我try 了子查询、合并、一些奇怪的XML路径之类的东西,但作为一个新的SQL用户,我不能理解这一点
我给出结果的原始查询是;
SELECT
tm.teammate_id AS "Teammate ID",
tm.name AS "Teammate name",
itt.team_id AS "Team IDs",
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 tm.teammate_id = itt.teammate_id