mysql - MYSQL中如何计算多个表的总和

Question

我有 3 个表，即:库存、消耗产品、损坏产品

库存:

|ID|TransactionDate     |Item             |Unit|Quantity|
---------------------------------------------------------
|1 |2019-07-10 12:23:51 |Plastic Cup 22oz |Pc  |200     |
---------------------------------------------------------
|2 |2019-07-10 01:23:51 |Plastic Cup 16oz |Pc  |100     |
---------------------------------------------------------
|3 |2019-07-10 01:23:51 |Plastic Cup 22oz |Pc  |100     |
---------------------------------------------------------
|4 |2019-07-10 01:23:51 |Lemon            |Pc  |100     |
---------------------------------------------------------

消费产品:

|ID|TID|TransactionDate     |Item           |Unit|Quantity|
---------------------------------------------------------

|1 |1  |2019-07-10 12:23:51 |Plastic Cup 22oz |Pc  |1     |
---------------------------------------------------------
|2 |1  |2019-07-10 01:23:51 |Lemon            |Pc  |1     |
---------------------------------------------------------
|3 |2  |2019-07-10 01:23:51 |Plastic Cup 16oz |Pc  |1     |
---------------------------------------------------------
|4 |2  |2019-07-10 01:23:51 |Lemon            |Pc  |1     |
---------------------------------------------------------
|5 |3  |2019-07-10 01:23:51 |Plastic Cup 16oz |Pc  |1     |
---------------------------------------------------------

损坏的产品:

|ID|TransactionDate     |Item             |Unit|Quantity|
---------------------------------------------------------
|1 |2019-07-10 12:23:51 |Plastic Cup 22oz |Pc  |10      |
---------------------------------------------------------
|2 |2019-07-10 01:23:51 |Plastic Cup 16oz |Pc  |10      |
---------------------------------------------------------
|3 |2019-07-10 01:23:51 |Plastic Cup 22oz |Pc  |5       |
---------------------------------------------------------
|4 |2019-07-10 01:23:51 |Lemon            |Pc  |6       |
---------------------------------------------------------

我怎样才能做出这样的输出？

库存:

|ID|Item             |Inv Bal|Consumed Prod|Dmgd Prod|Actual Balance
---------------------------------------------------------
|1 |Plastic Cup 22oz |300    |    1        |15       |284
---------------------------------------------------------
|2 |Plastic Cup 16oz |100    |    2        |10       |88
---------------------------------------------------------
|3 |Lemon            |100    |    2        |6        |92
---------------------------------------------------------

我尝试过其他方法，但它没有给我想要的结果。我只是一个有抱负的程序员，所以我们将非常感谢任何帮助。

SELECT 
  I.ID, I.Item, IFNULL(SUM(I.Quantity),0), 
  IFNULL(SUM(C.Quantity),0), IFNULL(SUM(D.Quantity),0), 
  IFNULL((IFNULL(SUM(I.Quantity),0) - (IFNULL(SUM(C.Quantity),0) + 
  IFNULL(SUM(D.Quantity),0))),0) AS NEW_BAL 
From 
 Inventory I 
 Left OUTER Join ConsumedProducts C 
 ON I.Item = C.Item 
 LEFT Outer Join DamagedProducts D 
 ON D.Item = I.Item 
GROUP BY I.Item, C.Item, D.Item

输出乘以另一个表的结果。

Answer 1

加入子查询如下:

SELECT 
  I.ID, I.Item, SUM(I.Quantity) as `Inv Bal`,
  D.Quantity as damagedQTY,
  C.Quantity as ConsumedQTY,
  SUM(I.Quantity) + D.Quantity -C.Quantity as NEWBALANCE  
From 
 Inventory I 
 LEFT OUTER JOIN
 ( select item,SUM(Quantity) as Quantity from DamagedProducts group by item) D on I.item=D.item
 LEFT OUTER JOIN
 (select item,SUM(Quantity) as Quantity from ConsumedProducts group by tID) C on I.item=C.item
GROUP BY I.Item