sql - rails/SQL : finding invoices by checking two sums

Question

我有一个 Invoice 模型，它有 has_many 行和 has_many 付款。

Invoice:
  id
  ref

Line:
  invoice_id:
  total (decimal)

Payment:
  invoice_id:
  total(decimal)

我需要找到所有已付款的发票。所以我正在做以下事情:

Invoice.joins(:lines, :payments).having(' sum(lines.total) = sum(payments.total').group('invoices.id')

哪些查询:

SELECT *
FROM "invoices"
INNER JOIN "lines" ON "lines"."invoice_id" = "invoices"."id"
INNER JOIN "payments" ON "payments"."invoice_id" = "invoices"."id"
GROUP BY invoices.id
HAVING sum(lines.total) = sum(payments.total)

但即使有已全额支付的发票，它也总是返回空数组。

我的代码有问题吗？

Answer 1

如果您以 1:n 关系连接到多个表，则连接的行可以彼此相乘。
这个相关的答案对问题有更详细的解释:

• Two SQL LEFT JOINS produce incorrect result

为避免这种情况，请在您加入之前合计总数。这样你就加入了 1(或 0)行，没有任何乘积。不仅正确，而且速度更快。

SELECT i.*, l.sum_total
FROM   invoices i
JOIN  (
   SELECT invoice_id, sum(total) AS sum_total
   FROM   lines
   GROUP  BY 1
   ) l ON l.invoice_id = i.id
JOIN  (
   SELECT invoice_id, sum(total) AS sum_total
   FROM   payments
   GROUP  BY 1
   ) p ON p.invoice_id = i.id 
WHERE l.sum_total = p.sum_total;

故意使用 [INNER] JOIN，而不是 LEFT [OUTER] JOIN。没有任何行或付款的发票一开始就没有意义。因为我们想要“已付”发票。由于缺乏定义和所提供查询的外观，我假设这意味着具有实际行和付款的发票，两者总计相同。