我继承了以下数据库设计.表是：
customers --------- customerid customernumber invoices -------- invoiceid amount invoicepayments --------------- invoicepaymentid invoiceid paymentid payments -------- paymentid customerid amount
我的查询需要返回给定客户编号的invoiceid,发票金额(在发票表中)和应付金额(发票金额减去对发票的任何付款).客户可能有多张发票.
当对发票进行多次付款时,以下查询为我提供了重复记录：
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'
我怎么解决这个问题？
解决方法
我不确定我有你,但这可能是你想要的：
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
如果每张发票有多个付款行,这将获得金额总和
