对三个表进行 SQL JOIN、GROUP BY 以获得总计

Question

我继承了以下数据库设计。表格是:

customers
---------
customerid  
customernumber

invoices
--------
invoiceid  
amount

invoicepayments
---------------
invoicepaymentid  
invoiceid  
paymentid

payments
--------
paymentid  
customerid  
amount

我的查询需要返回给定客户编号的发票 ID、发票金额(在发票表中)和应付金额(发票金额减去已向发票支付的所有付款)。一个客户可能有多张发票。

当对一张发票进行多次付款时，以下查询会给出重复的记录:

SELECT i.invoiceid, i.amount, i.amount - p.amount AS amountdue
FROM invoices i
LEFT JOIN invoicepayments ip ON i.invoiceid = ip.invoiceid
LEFT JOIN payments p ON ip.paymentid = p.paymentid
LEFT JOIN customers c ON p.customerid = c.customerid
WHERE c.customernumber = '100'

我该如何解决这个问题？

Answer 1

我不确定是否找到您，但这可能就是您正在寻找的:

SELECT i.invoiceid, sum(case when i.amount is not null then i.amount else 0 end), sum(case when i.amount is not null then i.amount else 0 end) - sum(case when p.amount is not null then p.amount else 0 end) AS amountdue
FROM invoices i
LEFT JOIN invoicepayments ip ON i.invoiceid = ip.invoiceid
LEFT JOIN payments p ON ip.paymentid = p.paymentid
LEFT JOIN customers c ON p.customerid = c.customerid
WHERE c.customernumber = '100'
GROUP BY i.invoiceid

如果每张发票有多个付款行，这将为您提供金额