mysql - 获取 2 个不同相关表中两个字段的总和

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(这是那里的付款 数量)是)。

我读过关于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;