SQL查询GROUP BY时如何避免STRING_AGG结果重复

问题描述 #

原始员工数据表 #

期望聚合结果 #

尝试的SQL语句 #

SELECT
    A.Name AS Name, A.Position AS Position, 
    STRING_AGG(B.Subordinate, ',') AS Sabordinates,
    STRING_AGG(A.Country, ',') AS Country, 
    SUM(C.Rate) 
FROM
    Emp A 
LEFT JOIN
    Sabordinate B ON A.ID = B.SabId  
LEFT JOIN
    Cost C ON C.SId = B.SabId
GROUP BY 
    A.Name, A.Position

当前错误结果 #

核心问题：分组后subordinate和Country字段出现重复值，需保留唯一值，同时保证Rate求和结果正确。

解决方案 #

重复值的根源是多表连接后产生了重复行，导致STRING_AGG聚合时重复拼接。以下两种方法可解决问题：

方法1：数据库兼容方案（先去重再聚合） #

通过CTE先提取管理者对应的唯一下属、国家，同时单独计算Rate总和，最后关联结果：

WITH UniqueSubordinates AS (
    SELECT DISTINCT
        A.Name,
        A.Position,
        B.Subordinate,
        A.Country
    FROM Emp A
    LEFT JOIN Sabordinate B ON A.ID = B.SabId
),
TotalRate AS (
    SELECT
        A.Name,
        A.Position,
        SUM(C.Rate) AS TotalRate
    FROM Emp A
    LEFT JOIN Sabordinate B ON A.ID = B.SabId
    LEFT JOIN Cost C ON C.SId = B.SabId
    GROUP BY A.Name, A.Position
)
SELECT
    us.Name,
    us.Position,
    STRING_AGG(us.Subordinate, ',') AS subordinate,
    STRING_AGG(DISTINCT us.Country, ',') AS Country,
    tr.TotalRate AS Rate
FROM UniqueSubordinates us
JOIN TotalRate tr ON us.Name = tr.Name AND us.Position = tr.Position
GROUP BY us.Name, us.Position, tr.TotalRate

方法2：简洁方案（仅支持部分数据库） #

如果使用SQL Server 2017+、PostgreSQL 9.0+等支持STRING_AGG中加DISTINCT的数据库，可直接在聚合函数内去重：

SELECT
    A.Name AS Name,
    A.Position AS Position,
    STRING_AGG(DISTINCT B.Subordinate, ',') AS subordinate,
    STRING_AGG(DISTINCT A.Country, ',') AS Country,
    SUM(C.Rate) AS Rate
FROM Emp A
LEFT JOIN Sabordinate B ON A.ID = B.SabId
LEFT JOIN Cost C ON C.SId = B.SabId
GROUP BY A.Name, A.Position

说明：两种方法都能保证Rate求和结果不变，同时去除subordinate和Country的重复值。

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