我继承了以下数据库设计.表是：
- 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
如果每张发票有多个付款行,这将获得金额总和
