我有3个表，我试图加入在一起，并得到总和.
发票 发票项目 付款
一张发票可以有多个项目.我可以把这些加在一起，得到正确的总和.
发票也可以有多个付款，这就是问题所在.付款表行似乎重复并返回错误的总额.
invoices个
| id | discount_rate |
|---|---|
| 40 | 0.25 |
| 41 | 0.25 |
| 42 | 0.1 |
invoice_items
| invoice_id | qty | cost | vat_rate |
|---|---|---|---|
| Cell 1 | Cell 2 | Cell 2 | Cell 2 |
| Cell 3 | Cell 4 | Cell 2 | Cell 2 |
我已经成功地连接了这两个表，并获得了正确的结果:
SELECT
invoices.id,
SUM((qty * cost - cost * qty * discount_rate) * vat_rate) + SUM(cost * qty) - SUM(cost * qty * discount_rate) final
FROM
invoices
JOIN
invoice_items ON invoices.id = invoice_items.invoice_id
GROUP BY invoice_id;
当我将Payments(一个发票可以有多个付款)表加入它时，问题就开始了. 付款
payments个
| invoice_id | amount |
|---|---|
| Cell 1 | Cell 2 |
| Cell 3 | Cell 4 |
Payments table with data:
-你在说什么？
with
paymentSumAggr as (
select invoice_id, sum(amount) as paymentSum from payments
group by invoice_id
)
select
invoices.id,
SUM((qty * cost - cost * qty * discount_rate) * vat_rate) + SUM(cost * qty) - SUM(cost * qty * discount_rate) final,
sum(p_sum.paymentSum) as payments_sum
from
invoices
left join
invoice_items ON invoices.id = invoice_items.invoice_id
left join (select * from paymentSumAggr) as p_sum on invoices.id = p_sum.invoice_id
group by invoices.id
Result from query:
我try 过以许多不同的方式连接这些应用程序，还try 了子查询和分组.