三个表上的 SQL JOIN、GROUP BY 以获取总数 [英] SQL JOIN, GROUP BY on three tables to get totals

问题描述

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

I've inherited the following DB design. Tables are:

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

invoices
--------
invoiceid  
amount

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

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

我的查询需要返回给定客户编号的 invoiceid、发票金额(在发票表中)和应付金额(发票金额减去已针对发票支付的任何款项).一个客户可能有多张发票.

My query needs to return invoiceid, the invoice amount (in the invoices table), and the amount due (invoice amount minus any payments that have been made towards the invoice) for a given customernumber. A customer may have multiple invoices.

当对发票进行多次付款时，以下查询为我提供了重复的记录:

The following query gives me duplicate records when multiple payments are made to an invoice:

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'

我该如何解决这个问题?

How can I solve this?

推荐答案

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

I am not sure I got you but this might be what you are looking for:

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

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

This would get you the amounts sums in case there are multiple payment rows for each invoice

这篇关于三个表上的 SQL JOIN、GROUP BY 以获取总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！