mysql - 计算字段上的 SUM 加倍

Question

我有一个 invoices 表。每张发票都有许多 invoice_items 和 transactions(或者，如果您愿意，也可以是“付款”)。对于每张发票，我想计算已支付金额(即其交易金额的总和)和总金额(即项目金额的总和)。

我编写了以下查询:

SELECT 
  invoices.*,
  SUM(transactions.amount_cents) AS amount_paid,
  SUM(invoice_items.quantity * invoice_items.price_cents) AS total
FROM invoices
RIGHT JOIN transactions 
  ON invoices.id = transactions.invoice_id
RIGHT JOIN invoice_items 
  ON invoices.id = invoice_items.invoice_id

但是，由于某些原因，total 字段的值会乘以交易数量(例如，如果我有一张总金额为 20 的发票，并且 < strong>2 笔交易，则total 字段为40)。

如果我从查询中删除对交易的所有引用:

SELECT 
  invoices.*,
  SUM(invoice_items.quantity * invoice_items.price_cents) AS total
FROM invoices
RIGHT JOIN invoice_items 
  ON invoices.id = invoice_items.invoice_id

total 字段被正确返回...

我不是这方面的专家，所以我可能在做一些非常愚蠢的事情。我已经尝试了 JOIN 的不同组合，但没有结果。

有什么提示吗？

编辑:半最终解决方案

我试图获得未付的发票，所以这是最后的查询:

SELECT 
  invoices.*,
  SUM(invoice_items.quantity * invoice_items.price_cents) AS total,
t.amount_paid
FROM invoices
LEFT JOIN invoice_items 
  ON invoice_items.invoice_id = invoices.id
LEFT JOIN (
  SELECT 
    invoice_id, 
    SUM(transactions.amount_cents) AS amount_paid
  FROM transactions 
  GROUP BY invoice_id
) t
  ON invoices.id = t.invoice_id
GROUP BY invoices.id
HAVING amount_paid >= total;

编辑:在意识到我不需要加入之后...

我真的不需要获取相关数据。我只想知道发票是已付款还是未付款。所以我最终使用了一个简单的 where:

SELECT `invoices`.* 
FROM `invoices` 
WHERE (
  (
    SELECT COALESCE(SUM(t.amount_cents), 0)
    FROM transactions t 
    WHERE t.invoice_id = invoices.id
  )
  >=
  (
    SELECT COALESCE(SUM(i.quantity * i.price_cents), 0)
    FROM invoice_items i
    WHERE i.invoice_id = invoices.id
  )
)

但感谢所有提供帮助的人。

Answer 1

您可以使用子查询将交易聚合到每个 invoice_id 一行，这样就不会导致出现额外的行。根据数量的不同，您可能希望将子查询分散到一个临时表中并引用该临时表。

SELECT 
  invoices.*,
  SUM(invoice_items.quantity * invoice_items.price_cents) AS total,
t.Amount_Paid
FROM invoices
LEFT JOIN invoice_items 
  ON invoice_items.invoice_id = invoices.id
LEFT JOIN (select invoice_id, SUM(transactions.amount_cents) as Amount_Paid from
transactions group by invoice_id) t
  ON invoices.id = t.invoice_id
GROUP BY invoices.id