SQL查询无重复A表记录并关联展示B、C表关联数据的实现方法

你现有写法返回重复A表记录的核心原因是多表左连时产生了笛卡尔积：当单条A记录同时关联多条B、C记录时，B和C的关联结果会交叉相乘，生成多条重复的A记录。

可以用STRING_AGG函数实现需求，核心思路是先分别聚合B、C表与A表的关联结果，再和A表做关联，避免多表联查产生的笛卡尔积，同时完成多值拼接。

实现SQL（兼容SQL Server、PostgreSQL、MySQL 8.0+） #

SELECT 
    A.Id,
    A.RefNumber,
    B_agg.LinkedB,
    B_agg.LinkedBRefNumb,
    C_agg.LinkedC,
    C_agg.LinkedCRefNumb
FROM A
-- 关联预聚合后的B表关联结果
LEFT JOIN (
    SELECT
        -- 提取关联关系中对应的A表ID
        IIF(le.EntityId = B.Id, le.LinkedEntityId, le.EntityId) AS A_Id,
        STRING_AGG(B.Id, ',') AS LinkedB,
        STRING_AGG(B.RefNumber, ',') AS LinkedBRefNumb
    FROM LinkedEntity le
    INNER JOIN B ON B.Id IN (le.EntityId, le.LinkedEntityId)
    GROUP BY IIF(le.EntityId = B.Id, le.LinkedEntityId, le.EntityId)
) B_agg ON A.Id = B_agg.A_Id
-- 关联预聚合后的C表关联结果
LEFT JOIN (
    SELECT
        IIF(le.EntityId = C.Id, le.LinkedEntityId, le.EntityId) AS A_Id,
        STRING_AGG(C.Id, ',') AS LinkedC,
        STRING_AGG(C.RefNumber, ',') AS LinkedCRefNumb
    FROM LinkedEntity le
    INNER JOIN C ON C.Id IN (le.EntityId, le.LinkedEntityId)
    GROUP BY IIF(le.EntityId = C.Id, le.LinkedEntityId, le.EntityId)
) C_agg ON A.Id = C_agg.A_Id

逻辑说明 #

1. 预聚合阶段分别处理B、C表和关联表的关系：每条关联记录中，只要Id属于B/C表，另一端的Id就是对应的A表Id，按A表Id分组后用STRING_AGG拼接多值，确保每个A的Id对应唯一一条聚合结果
2. 最后将两份预聚合结果左连到A表，不会产生交叉笛卡尔积，也就不会出现重复的A表记录

兼容说明 #

• 如果使用MySQL 5.x版本，将STRING_AGG替换为GROUP_CONCAT即可实现相同效果
• 若数据库不支持IIF函数，可替换为等价的CASE WHEN写法：CASE WHEN le.EntityId = B.Id THEN le.LinkedEntityId ELSE le.EntityId END

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