我有两张table，TX和最早的。我尝试根据“UserId”和“AcceptorCode”加入它们，并且只返回tx中的“CreatedOn”大于或等于earliest中的“EarliestPayDate”的记录。

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时，它会将数量相加三次。

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