MS Access多表左连接时Sum()返回错误值的问题求助

问题出在多表关联时产生了笛卡尔积：当一个订单同时关联多条Order_Lines记录和多条Order_Payments记录时，两个子表的记录会交叉匹配，导致每条明细和每条支付记录组合生成重复行，最终求和时数值被重复计算。

要避免这个问题，需要先分别对订单的明细总额、支付总额按订单ID单独汇总，再将汇总结果关联到客户和订单表，消除交叉重复。

方法1：使用子查询直接关联 #

SELECT 
    Customers.ID, Customers.Name, Customers.Address, Customers.Phone,
    Nz(OrderTotals.TotalBalance, 0) AS [Total Balance],
    Nz(PaymentTotals.PaymentsTotal, 0) AS [Payments Total]
FROM 
    Customers
LEFT JOIN (
    SELECT 
        Orders.Customer_Id,
        Orders.ID AS OrderID,
        SUM(Order_Lines.Subtotal) AS TotalBalance
    FROM Orders
    LEFT JOIN Order_Lines ON Orders.ID = Order_Lines.Order_ID
    GROUP BY Orders.Customer_Id, Orders.ID
) AS OrderTotals ON Customers.ID = OrderTotals.Customer_Id
LEFT JOIN (
    SELECT 
        Orders.Customer_Id,
        Orders.ID AS OrderID,
        SUM(Order_Payments.Amount) AS PaymentsTotal
    FROM Orders
    LEFT JOIN Order_Payments ON Orders.ID = Order_Payments.Order_ID
    GROUP BY Orders.Customer_Id, Orders.ID
) AS PaymentTotals ON Customers.ID = PaymentTotals.Customer_Id AND OrderTotals.OrderID = PaymentTotals.OrderID
GROUP BY 
    Customers.ID, Customers.Name, Customers.Address, Customers.Phone,
    Nz(OrderTotals.TotalBalance, 0), Nz(PaymentTotals.PaymentsTotal, 0);

方法2：先创建两个独立汇总查询（更适合Access可视化操作） #

1. 创建Order_Totals查询，按订单汇总明细金额：

SELECT 
    Orders.Customer_Id,
    Orders.ID AS OrderID,
    SUM(Order_Lines.Subtotal) AS TotalBalance
FROM Orders
LEFT JOIN Order_Lines ON Orders.ID = Order_Lines.Order_ID
GROUP BY Orders.Customer_Id, Orders.ID;

2. 创建Payment_Totals查询，按订单汇总支付金额：

SELECT 
    Orders.Customer_Id,
    Orders.ID AS OrderID,
    SUM(Order_Payments.Amount) AS PaymentsTotal
FROM Orders
LEFT JOIN Order_Payments ON Orders.ID = Order_Payments.Order_ID
GROUP BY Orders.Customer_Id, Orders.ID;

3. 最后关联客户表和这两个汇总查询：

SELECT 
    Customers.ID, Customers.Name, Customers.Address, Customers.Phone,
    Nz(Order_Totals.TotalBalance, 0) AS [Total Balance],
    Nz(Payment_Totals.PaymentsTotal, 0) AS [Payments Total]
FROM 
    ((Customers
LEFT JOIN Order_Totals ON Customers.ID = Order_Totals.Customer_Id)
LEFT JOIN Payment_Totals ON Customers.ID = Payment_Totals.Customer_Id AND Order_Totals.OrderID = Payment_Totals.OrderID)
GROUP BY 
    Customers.ID, Customers.Name, Customers.Address, Customers.Phone,
    Nz(Order_Totals.TotalBalance, 0), Nz(Payment_Totals.PaymentsTotal, 0);

补充说明 #

• 使用Nz()函数是为了处理没有订单、明细或支付记录的客户，确保返回0而非空值。
• 两种方法核心都是先按订单维度单独汇总，再关联，从根源避免笛卡尔积导致的重复计算。

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