Mysql Left Join with Sum Criteria

Question

我正在尝试创建一个报告，该报告将显示具有未清帐户余额的客户，但我似乎无法使用左连接来让它工作。

我有 3 个表 - 客户、购买和付款，我试图显示其总购买成本减去付款大于 0 的客户。

到目前为止，我有以下查询:

mysqli_query("SELECT customers.name as name, SUM(purchases.cost) as cost, SUM(payments.paymentamount) as payments FROM customers 
LEFT JOIN purchases ON purchases.customerid = customers.id 
LEFT JOIN payments ON payments.customerid = customers.id 
WHERE (cost - payments) > 0 
GROUP BY customers.id");

谁能看出为什么这不起作用？

感谢您的帮助!

Answer 1

您需要为每个 customerID 计算子查询的每个总数，以避免不正确的结果。

SELECT  a.*,
        COALESCE(b.totalPurchase , 0) - COALESCE(c.totalPayment , 0) AS Balance
FROM    customers a
        LEFT JOIN
        (
            SELECT  customerid, SUM(cost) totalPurchase
            FROM    purchases
            GROUP   BY customerid
        ) b ON a.ID = b.customerID
        LEFT JOIN
        (
            SELECT  customerid, SUM(paymentamount) totalPayment
            FROM    payments
            GROUP   BY customerid
        ) c ON a.ID = c.customerID
WHERE   COALESCE(b.totalPurchase , 0) - COALESCE(c.totalPayment , 0) > 0