具有sum(*) = sum(*)的SQL

Question

关于使用词条，我有一个简短的问题。

我有两张桌子。一张发票，一张付款。这是我想使用的select语句，用于给我未全部支付的发票：

select * 
from invoices i 
where not exists (select * 
                  from payments p 
                  having sum(p.amount) = sum(i.amount))

这不管用。有人知道解决办法吗？

Answer 1

让我们假设发票和支付表是在invoiceid上连接的，在这种情况下，您可以加入它们并指定联接的条件

选择i.invoiceid，i.amount，.(您希望从发票/付款中获得的其他信息)

从发票一，付款p

其中i.invoiceid = p.invoiceid

和p.amount <> i.amount

Answer 2

您的想法是正确的，但您需要考虑以下问题：

1. 的付款金额是多少？
2. 的发票金额是多少？
3. 发票和付款表之间有什么关系？

假设：

• 在两个表中都有一个invoice_id (或类似的)：

，

• ，发票表中的每个invoice_id可以包含多个行项，
• ，每个invoice_id在支付表中可以包含多个行项

然后，如果您想返回发票表中的所有行：

SELECT x.*,--get the invoice and all invoice line items
       y.invoice_amount AS total_invoice_payments_received --the current amount received
FROM (SELECT *,
             SUM(amount) OVER (PARTITION BY invoice_id) AS invoice_amount
      FROM invoices) x
  LEFT JOIN (SELECT invoice_id,
                    SUM(amount) AS invoice_amount
             FROM payments
             GROUP BY invoice_id) y ON x.invoice_id = y.invoice_id
WHERE x.invoice_amount <> COALESCE(y.invoice_amount,0); --in case there are no payment records

如果发票表中每张发票只有一行，那么它就更简单了：

SELECT x.*,--get the invoice and all invoice line items
       y.invoice_amount AS total_invoice_payments_received --the amount in payments
FROM invoices x
  LEFT JOIN (SELECT invoice_id,
                    SUM(amount) AS invoice_amount
             FROM payments
             GROUP BY invoice_id) y ON x.invoice_id = y.invoice_id
WHERE x.invoice_amount <> COALESCE(y.invoice_amount,0); --in case there are no payment records