mysql - 将值之和与差异表中相同 ID 的其他值进行比较

Question

我的 mysql 脚本有问题。

我有 2 个表，我想将我的 invoiceAmount 与另一个表中的 paymentAmount 进行比较。情况是:一张发票可以进行多次付款，我想将invoiceAmount(表A)与 paymentAmount(表B)进行比较。

我已经加入了计算出的相同发票ID的 paymentAmount ，我的问题是如何将它( paymentAmount(表B)之和与 invoiceAmount(表 A) 两个表中所有行的相同发票 ID)？真的需要你们的帮助..

我的脚本是:

SELECT invoice.InvoiceID, invoice.InvoiceAmount, pays.InvoiceID, sum(pays.PaymentAmount) as pays, invoice.InvoiceDate 
FROM `payment from customer` as pays
JOIN invoice ON(pays.InvoiceID=invoice.InvoiceID)
WHERE pays.InvoiceID in (
    SELECT InvoiceID 
    FROM `payment from customer`
    GROUP BY InvoiceID
    HAVING count(*) > 1)
GROUP BY pays.InvoiceID

Answer 1

SELECT * 
FROM (select invoice.InvoiceID, 
             invoice.InvoiceAmount, 
             pays.InvoiceID, 
             pays.PaymentAmount as pays, 
             invoice.InvoiceDate ,
             (select sum(a.PaymentAmount) 
                from `payment from customer` a 
               where a.InvoiceID = invoice.InvoiceID) totalPays
        from `payment from customer` as pays JOIN invoice 
          ON (pays.InvoiceID=invoice.InvoiceID) ) temp
where InvoiceDate > totalPays