在两个不同的相关表中获得两个字段的总和

Question

我想在两个相关的表中获得amount列的总和。

发票表：

----------------------------------------- 
| id | student_id | created | updated | 
----------------------------------------- 
| 5 |  25  | date | date | 
----------------------------------------- 

发票项目表：

------------------------------ 
| id | invoice_id | amount | 
------------------------------ 
| 1 |  5  | 250 | 
------------------------------ 
| 2 |  5  | 100 | 
------------------------------ 
| 3 |  5  | 40  | 
------------------------------ 

付款表：

------------------------------ 
| id | invoice_id | amount | 
------------------------------ 
| 1 |  5  | 100 | 
------------------------------ 
| 2 |  5  | 290 | 
------------------------------ 

所需的输出：

-------------------------------------- 
| id | invoiceTotal | paymentTotal | 
-------------------------------------- 
| 1 |  390  | 390  | 
-------------------------------------- 

我已经试过

SELECT 
    i.id, 
    sum(ii.amount) as invoiceTotal, 
    sum(p.amount) as paymentTotal 
FROM 
    invoices i 
LEFT JOIN 
    invoice_items ii ON i.id = ii.invoice_id 
LEFT JOIN 
    payments p ON i.id = p.invoice_id 
WHERE 
    i.student_id = '25' 
GROUP BY 
    i.id 

什么这似乎做的是正确计算支付的总和，但invoice_items.amount似乎已经由6重复查询（这是payments的编号）。

我已阅读过SO here和here上的类似问题，但这些例子比我想要做的要复杂得多，我无法弄清楚要放在哪里。

Answer 1

连接会导致笛卡尔产品出现问题。如果学生有多个发票项目和付款，那么总数将是错误的。

一种方法最适合所有发票是union all/group by方法：

select i.id, sum(invoiceTotal) as invoiceTotal, sum(paymentTotal) as paymentTotal 
from ((select i.id, 0 as invoiceTotal, 0 as paymentTotal 
     from invoices i 
    ) union all 
     (select ii.invoiceId, sum(ii.amount) as invoiceTotal, NULL 
     from invoiceitems ii 
     group by ii.invoiceId 
    ) union all 
     (select p.invoiceId, 0, sum(p.amount) as paymentTotal 
     from payments p 
     group by p.invoiceId 
    ) 
    ) iip 
group by id; 

对于一个学生，我会建议相关子查询：

select i.id, 
     (select sum(ii.amount) 
     from invoiceitems ii 
     where ii.invoiceid = i.id 
     ) as totalAmount, 
     (select sum(p.amount) 
     from payment p 
     where p.invoiceid = i.id 
     ) as paymentAmount 
from invoices i 
where i.studentid = 25;