如何按PerformanceMonth统计当月账户余额及下月对应账户付款总额？

原查询的问题 #

• 直接LEFT JOIN会让account表的记录被payments表的多条付款记录重复匹配，导致SUM(a.Balance)被重复计算，结果失真。
• 仅用MONTH()函数判断月份匹配，没考虑年份，跨年场景（比如12月的下一月是次年1月）会出现错误匹配。
• 未提前聚合付款数据，关联后的求和逻辑容易出错。

修正后的SQL方案 #

方案一：使用CTE（支持CTE的数据库如MySQL 8.0+、PostgreSQL等） #

WITH monthly_payments AS (
    SELECT 
        accountno,
        DATE_FORMAT(paymentdate, '%Y-%m') AS payment_month,
        SUM(paymentamount) AS total_payment
    FROM payments
    GROUP BY accountno, payment_month
)
SELECT 
    a.PerformanceMonth,
    COUNT(DISTINCT a.accountno) AS 当月唯一账户总数,
    SUM(a.Balance) AS 当月账户总余额,
    COALESCE(SUM(mp.total_payment), 0) AS 下一月付款总额
FROM account a
LEFT JOIN monthly_payments mp 
    ON a.accountno = mp.accountno
    AND mp.payment_month = DATE_FORMAT(DATE_ADD(a.PerformanceMonth, INTERVAL 1 MONTH), '%Y-%m')
GROUP BY a.PerformanceMonth;

方案二：使用子查询（兼容低版本数据库） #

SELECT 
    a.PerformanceMonth,
    COUNT(DISTINCT a.accountno) AS 当月唯一账户总数,
    SUM(a.Balance) AS 当月账户总余额,
    COALESCE(SUM(mp.total_payment), 0) AS 下一月付款总额
FROM account a
LEFT JOIN (
    SELECT 
        accountno,
        DATE_FORMAT(paymentdate, '%Y-%m') AS payment_month,
        SUM(paymentamount) AS total_payment
    FROM payments
    GROUP BY accountno, payment_month
) mp 
    ON a.accountno = mp.accountno
    AND mp.payment_month = DATE_FORMAT(DATE_ADD(a.PerformanceMonth, INTERVAL 1 MONTH), '%Y-%m')
GROUP BY a.PerformanceMonth;

关键优化点 #

• 提前聚合payments表：按账户和付款年月分组计算总付款，避免关联时的记录重复。
• 全年月匹配：用%Y-%m格式的字符串匹配下一月，确保跨年场景的正确性。
• 避免余额重复计算：先基于account表聚合账户数和余额，再关联预处理后的付款数据。

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