SQL JOIN,GROUP BY在三个表上获取总计

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

如果每张发票有多个付款行,这将获得金额总和

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!