如何在LISTAGG聚合中获取去重后的唯一值列表？

要解决LISTAGG生成重复值的问题，核心思路是先确保聚合前的数据无重复，或者使用支持去重的聚合函数语法，以下分不同数据库场景说明：

Oracle数据库 #

方法1：先去重再聚合（兼容所有版本） #

通过子查询对table2的关联字段和目标字段去重，再关联table1进行LISTAGG聚合：

SELECT
    t1.id AS ID,
    LISTAGG(t2.object_id, ';') WITHIN GROUP (ORDER BY t2.object_id) AS my_list
FROM 
    table1 t1
JOIN (
    SELECT DISTINCT tbl1_id, object_id
    FROM table2
) t2 ON t1.id = t2.tbl1_id
GROUP BY t1.id

方法2：使用LISTAGG的DISTINCT参数（Oracle 19c及以上） #

Oracle 19c开始支持在LISTAGG中直接使用DISTINCT关键字去重，写法更简洁：

SELECT
    table1.id AS ID,
    LISTAGG(DISTINCT table2.object_id, ';') WITHIN GROUP (ORDER BY table2.object_id) AS my_list
FROM 
    table1
JOIN table2 ON table1.id = table2.tbl1_id
GROUP BY table1.id

PostgreSQL数据库 #

PostgreSQL没有原生LISTAGG函数，替代的STRING_AGG支持DISTINCT参数，直接去重聚合：

SELECT
    table1.id AS ID,
    STRING_AGG(DISTINCT table2.object_id::TEXT, ';' ORDER BY table2.object_id) AS my_list
FROM 
    table1
JOIN table2 ON table1.id = table2.tbl1_id
GROUP BY table1.id

注：如果object_id是数值类型，需要转为TEXT类型才能参与STRING_AGG聚合

SQL Server数据库 #

SQL Server的STRING_AGG同样支持DISTINCT参数，写法如下：

SELECT
    table1.id AS ID,
    STRING_AGG(DISTINCT table2.object_id, ';') WITHIN GROUP (ORDER BY table2.object_id) AS my_list
FROM 
    table1
JOIN table2 ON table1.id = table2.tbl1_id
GROUP BY table1.id

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