SQL双JOIN查询出现重复数据 如何实现两表字段分别求和去重输出

问题原因 #

直接关联两个独立子表会产生笛卡尔积：单个人员ID对应4条dbo_Z_Ferien记录和96条dbo_Z_ERFASSUNG记录，关联后会生成4×96=384条冗余数据，导致记录重复、求和结果错误。

解决方案 #

根据你需要的结果格式二选一即可：

方案1：每个人员ID输出1行汇总结果

先分别对两个子表做聚合运算，再和主表关联，从根源避免笛卡尔积：

SELECT 
    p.LPE_ID AS PersID,
    ISNULL(e.sum_erf, 0) AS Erf,
    ISNULL(f.sum_fer, 0) AS Fer
FROM dbo_A_PERSONAL p
LEFT JOIN (
    SELECT 
        ZER_LPE,
        SUM(ZER_Std100) AS sum_erf
    FROM dbo_Z_ERFASSUNG
    WHERE ZER_EIGENSCH = 3
    GROUP BY ZER_LPE
) e ON p.LPE_ID = e.ZER_LPE
LEFT JOIN (
    SELECT 
        ZFE_LPE_ID,
        SUM(ZFE_TAGE) AS sum_fer
    FROM dbo_Z_Ferien
    GROUP BY ZFE_LPE_ID
) f ON p.LPE_ID = f.ZFE_LPE_ID
WHERE p.LPE_ID = 804

方案2：输出示例中的分条展示格式

用UNION ALL拼接两个子表的查询结果，无对应值的字段填0即可匹配你给出的示例格式：

SELECT 
    ZER_LPE AS PersID,
    ZER_Std100 AS Erf,
    0 AS Fer
FROM dbo_Z_ERFASSUNG
WHERE ZER_EIGENSCH = 3 AND ZER_LPE = 804

UNION ALL

SELECT 
    ZFE_LPE_ID AS PersID,
    0 AS Erf,
    ZFE_TAGE AS Fer
FROM dbo_Z_Ferien
WHERE ZFE_LPE_ID = 804

ORDER BY PersID, Erf DESC

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