如何按某列分组后，对另一列去重并拼接？

原始数据 #

Party_Id    Account_Id  TR_Id
1           1           6
1           2           6
2           3           5
2           4           5
3           6           7
3           7           8

期望结果 #

Party   Trading
1        6
2        5
3        7,8

需求说明 #

按Party_Id分组，对TR_Id列去重后拼接为逗号分隔的字符串。

各数据库实现方案 #

MySQL/MariaDB

利用GROUP_CONCAT配合DISTINCT实现：

SELECT 
    Party_Id AS Party,
    GROUP_CONCAT(DISTINCT TR_Id ORDER BY TR_Id SEPARATOR ',') AS Trading
FROM your_table_name
GROUP BY Party_Id;

• DISTINCT确保TR_Id去重后再拼接
• ORDER BY保证拼接顺序与示例一致

SQL Server 2017+

使用STRING_AGG函数：

SELECT 
    Party_Id AS Party,
    STRING_AGG(DISTINCT TR_Id, ',') WITHIN GROUP (ORDER BY TR_Id) AS Trading
FROM your_table_name
GROUP BY Party_Id;

SQL Server 2016及以下

通过FOR XML PATH方式实现：

SELECT 
    Party_Id AS Party,
    STUFF((SELECT DISTINCT ',' + CAST(TR_Id AS VARCHAR(10))
           FROM your_table_name t2
           WHERE t2.Party_Id = t1.Party_Id
           ORDER BY ',' + CAST(TR_Id AS VARCHAR(10))
           FOR XML PATH('')), 1, 1, '') AS Trading
FROM your_table_name t1
GROUP BY Party_Id;

PostgreSQL

使用STRING_AGG函数，注意转换数据类型：

SELECT 
    Party_Id AS Party,
    STRING_AGG(DISTINCT TR_Id::TEXT, ',' ORDER BY TR_Id) AS Trading
FROM your_table_name
GROUP BY Party_Id;

内容的提问来源于stack exchange，提问作者Koala.Et