Oracle多表连接逗号分隔值聚合 解决笛卡尔积重复问题

重复值的根源是两张一对多关系的子表直接关联主表时，会为每个学生生成mn行的笛卡尔积（m为该学生关联的图书数量，n为该学生关联的钢笔数量），直接在关联结果上聚合就会出现字符串重复拼接、数值重复统计的问题*。
正确的解决思路是先单独对每个子表按学生ID完成聚合，保证每个学生ID在子表聚合结果中仅对应1行，再关联到学生主表，从根源避免交叉乘积。

可直接运行的正确SQL #

SELECT 
    s.StudentID,
    s.Name,
    s.Age,
    b.BookNames,
    COALESCE(b.TotalBookPrice, 0) AS TotalBookPrice,
    p.PenBrands,
    COALESCE(p.TotalPenPrice, 0) AS TotalPenPrice
FROM Student s
LEFT JOIN (
    -- 先聚合图书表：每个学生仅返回1行聚合结果
    SELECT 
        SID,
        LISTAGG(BookName, ', ') WITHIN GROUP (ORDER BY BookID) AS BookNames,
        SUM(BookPrice) AS TotalBookPrice
    FROM Book
    GROUP BY SID
) b ON s.StudentID = b.SID
LEFT JOIN (
    -- 先聚合钢笔表：每个学生仅返回1行聚合结果
    SELECT 
        SID,
        LISTAGG(PenBrandName, ', ') WITHIN GROUP (ORDER BY PenID) AS PenBrands,
        SUM(PenPrice) AS TotalPenPrice
    FROM Pen
    GROUP BY SID
) p ON s.StudentID = p.SID;

逻辑说明 #

• 两个子查询提前完成单表维度的聚合，关联到学生表时不会产生多对多的行膨胀，彻底解决笛卡尔积导致的重复问题
• COALESCE函数用于处理学生无对应图书/钢笔的场景，将空值总价转换为0，匹配预期输出
• 字符串拼接分隔符使用, （逗号+空格），和目标结果格式完全对齐
• 执行后返回结果和预期输出完全一致，无重复值问题

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