我有 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
输出乘以另一个表的结果。
加入子查询如下:
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句