SQL JOIN查询结果重复三次，聚合时金额重复求和问题求助

问题 #

我有tx和earliest两个CTE表，尝试基于UserId和AcceptorCode进行关联，仅返回tx中CreatedOn大于等于earliest中EarliestPayDate的记录。最初使用如下SQL查询时，每条结果返回三次：

WITH tx AS
(
    SELECT 
        "AcceptorCode", 
        "UserId", 
        "CreatedOn",
        "Amount",
        "WithdrawType",
        "DepositType",
        "Balance"
    FROM 
        "public"."Transactions"
    WHERE 
       -- "AcceptorCode" IS NOT NULL AND 
       -- "AcceptorCode" != ''
    "CreatedOn" BETWEEN {{START_DATE}} AND {{END_DATE}}
), 
earliest AS
(
    SELECT 
        "AcceptorCode", 
        "UserId", 
        MIN(CAST("CreatedOn" AS date)) AS "EarliestPayDate"
    FROM 
        tx
    WHERE 
        "AcceptorCode" IN ('000000014578478', '000000900030378', '000000005227597')
    GROUP BY 
        "AcceptorCode", 
        "UserId"
), 
wages AS 
(
    SELECT 
        e."UserId", t.*
    FROM 
        tx t
    JOIN 
        earliest e ON t."UserId" = e."UserId" AND (t."AcceptorCode" IS NULL OR t."AcceptorCode" = '')
    WHERE 
        t."CreatedOn" >= e."EarliestPayDate"
    ORDER BY 
        e."UserId" DESC   
)
SELECT *
FROM wages

使用SELECT DISTINCT "UserId"可解决基础重复问题，但使用聚合函数和GROUP BY时，金额会被重复求和三次，对应的SQL如下：

WITH tx AS
(
    ...
), 
earliest AS
(
    ...
), 
wages AS 
(
    SELECT 
        e."UserId", 
        SUM(CASE WHEN t."WithdrawType" IN (2, 3) THEN t."Amount" ELSE 0 END) AS "TotalWithdrawalWage",
        SUM(CASE WHEN t."DepositType" = 4 THEN t."Amount" ELSE 0 END) AS "TotalDepositWage"
    FROM 
        tx t
    JOIN 
        earliest e ON t."UserId" = e."UserId"
    WHERE 
        t."CreatedOn" >= e."EarliestPayDate"
    GROUP BY 
        e."UserId"
    ORDER BY 
        e."UserId" DESC   
)
SELECT *, "TotalWithdrawalWage" - "TotalDepositWage" AS "Difference"
FROM wages
WHERE "TotalWithdrawalWage" > 0 OR "TotalDepositWage" > 0
ORDER BY "UserId" DESC    

请问如何修改查询，让每条结果仅返回一次且聚合计算正确？

解决思路与修正后的SQL #

重复原因 #

问题出在earliest表的分组逻辑：你按AcceptorCode和UserId分组，而AcceptorCode包含三个指定值，这会导致同一个UserId对应多条earliest记录（每个匹配的AcceptorCode各一条）。当tx和earliest仅通过UserId关联时，单条tx记录会与该UserId对应的所有earliest记录匹配，从而重复返回三次，聚合时金额也会被重复计算三次。

修正方案 #

调整earliest表，确保每个UserId只保留最早的EarliestPayDate（而非每个AcceptorCode+UserId组合的最早日期），这样关联时不会产生一对多的匹配。

修正后的完整SQL：

WITH tx AS
(
    SELECT 
        "AcceptorCode", 
        "UserId", 
        "CreatedOn",
        "Amount",
        "WithdrawType",
        "DepositType",
        "Balance"
    FROM 
        "public"."Transactions"
    WHERE 
        "CreatedOn" BETWEEN {{START_DATE}} AND {{END_DATE}}
), 
earliest AS
(
    -- 先按AcceptorCode+UserId取各组合的最早日期，再按UserId取全局最早日期
    SELECT 
        "UserId",
        MIN("EarliestPayDate") AS "EarliestPayDate"
    FROM (
        SELECT 
            "AcceptorCode", 
            "UserId", 
            MIN(CAST("CreatedOn" AS date)) AS "EarliestPayDate"
        FROM 
            tx
        WHERE 
            "AcceptorCode" IN ('000000014578478', '000000900030378', '000000005227597')
        GROUP BY 
            "AcceptorCode", 
            "UserId"
    ) AS user_acceptor_earliest
    GROUP BY 
        "UserId"
), 
wages AS 
(
    SELECT 
        e."UserId", 
        SUM(CASE WHEN t."WithdrawType" IN (2, 3) THEN t."Amount" ELSE 0 END) AS "TotalWithdrawalWage",
        SUM(CASE WHEN t."DepositType" = 4 THEN t."Amount" ELSE 0 END) AS "TotalDepositWage"
    FROM 
        tx t
    JOIN 
        earliest e ON t."UserId" = e."UserId"
    WHERE 
        t."CreatedOn" >= e."EarliestPayDate"
        -- 保留原查询中对tx的AcceptorCode过滤条件（如果业务需要）
        AND (t."AcceptorCode" IS NULL OR t."AcceptorCode" = '')
    GROUP BY 
        e."UserId"
    ORDER BY 
        e."UserId" DESC   
)
SELECT 
    *, 
    "TotalWithdrawalWage" - "TotalDepositWage" AS "Difference"
FROM wages
WHERE "TotalWithdrawalWage" > 0 OR "TotalDepositWage" > 0
ORDER BY "UserId" DESC    

关键修改点 #

1. 重构earliest表：新增内层子查询先按AcceptorCode+UserId取各组合的最早日期，再外层按UserId取该用户的全局最早日期，确保每个UserId仅对应一条记录。
2. 关联逻辑稳定：此时tx与earliest通过UserId关联时是一对一匹配，不会产生重复数据，聚合计算自然准确。
3. 保留业务过滤条件：将原查询中wages里对tx.AcceptorCode的过滤条件移到WHERE子句中，确保业务逻辑不变。

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