MS Access中Left Join结合Sum/Group By查询问题及周期分组需求

我在MS Access中执行包含left join、sum、group by和order by的SQL查询时，返回结果不正确，希望将日期字段转换为周期格式（如Aug-24、Sep-24），并得到正确的月度汇总结果。

表Expense数据 #

ID  DATE        INVONO      TRANSACTION TOTEXP
----------------------------------------------
1   29-08-2024  EXP-1001    EXPENSE     25000
2   30-08-2024  EXP-1002    EXPENSE     25000
3   29-09-2024  EXP-1003    EXPENSE     30000
5   29-09-2024  EXP-1004    EXPENSE     30000

表Invoice数据 #

DATE        INVONO      TRANSACTION    TOTPRP   TOTPRS  PAYMENT
---------------------------------------------------------------
29-08-2024  SALES-1000  SALES           100000  150000  150000
30-08-2024  SALES-1001  SALES           300000  350000  350000
29-09-2024  SALES-1002  SALES           200000  250000  250000
29-09-2024  SALES-1003  SALES           200000  250000  250000
30-09-2024  SALES-1004  SALES           250000  300000  
30-09-2024  SALES-1005  SALES           250000  300000  

原查询语句 #

SELECT 
    Invoice.Date AS [DATE],
    SUM(Invoice.TotPRP) AS TOTPRP, 
    SUM(Invoice.TotPRS) AS TOTPRS, 
    SUM(Invoice.PAYMENT) AS PAYMENT, 
    SUM(Expense.Totexp) AS TOTEXP, 
    SUM(Invoice.TotPRS) - SUM(Invoice.TotPRP) AS TOTRESULT,
    SUM(Invoice.PAYMENT) - SUM(Invoice.TotPRS) AS TOTOUTSTANDING,
    SUM(Invoice.PAYMENT) - (SUM(Invoice.TotPRP) + SUM(Expense.TotEXP)) AS TOTPROFITNET
FROM 
    Invoice 
LEFT JOIN 
    Expense ON Invoice.Date = Expense.Date
GROUP BY 
    Invoice.Date, Invoice.INVONO
ORDER BY 
    Invoice.Date;

当前错误结果 #

期望结果 #

问题分析 #

原查询存在两个核心问题：

1. 分组维度错误：按Invoice.Date和Invoice.INVONO分组，导致结果按单个发票而非月度汇总，出现重复的日期行。
2. 关联后数据重复计算：直接用Invoice LEFT JOIN Expense ON Invoice.Date = Expense.Date会产生笛卡尔积（同一天有多条发票和费用记录时，每条发票会关联所有当天的费用记录），最终SUM(Expense.Totexp)会被重复统计，结果失真。

修正后的SQL语句 #

正确的做法是先分别对Invoice和Expense按月度汇总数据，再将两个汇总后的结果关联，避免重复计算：

SELECT
    inv.Period,
    inv.TOTPRP,
    inv.TOTPRS,
    inv.PAYMENT,
    Nz(exp.TOTEXP, 0) AS TOTEXP,
    inv.TOTPRS - inv.TOTPRP AS TOTRESULT,
    inv.PAYMENT - inv.TOTPRS AS TOTOUTSTANDING,
    inv.PAYMENT - (inv.TOTPRP + Nz(exp.TOTEXP, 0)) AS TOTPROFITNET
FROM
    (
        SELECT
            Format([DATE], "mmm-yy") AS Period,
            SUM(TotPRP) AS TOTPRP,
            SUM(TotPRS) AS TOTPRS,
            SUM(Nz(PAYMENT, 0)) AS PAYMENT
        FROM Invoice
        GROUP BY Format([DATE], "mmm-yy"), DateSerial(Year([DATE]), Month([DATE]), 1)
    ) AS inv
LEFT JOIN
    (
        SELECT
            Format([DATE], "mmm-yy") AS Period,
            SUM(Totexp) AS TOTEXP
        FROM Expense
        GROUP BY Format([DATE], "mmm-yy"), DateSerial(Year([DATE]), Month([DATE]), 1)
    ) AS exp ON inv.Period = exp.Period
ORDER BY DateSerial(Year(DateValue(inv.Period & "-01")), Month(DateValue(inv.Period & "-01")), 1);

说明 #

1. 日期转周期：用Format([DATE], "mmm-yy")将日期转换为Aug-24格式的周期。
2. 子查询汇总：两个子查询分别按月度汇总发票和费用数据，确保每个周期只有一条汇总记录，避免关联时的笛卡尔积。
3. 处理空值：用Nz()函数处理空值（比如无费用的周期、未支付的发票），确保计算结果不为空。
4. 正确排序：通过DateSerial基于周期生成当月第一天，保证结果按时间顺序排列，避免Aug-24和Sep-24因字符串排序出现问题。

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