mysql - 按交易日期从以前的交易中获取总项目数量并获取当前项目总数量

Question

我有 3 个表，即:库存:

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

消费品:

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

损坏的产品:

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

目标输出:假设当前日期是 (2019-07-10)

1. Prev Bal= Inventory -(Cunsumed + Damaged)“从当前日期之前的日期开始”
2. 今天交付的项目(反射(reflect)在“库存”表上)
3. 当前消耗(反射(reflect)在“ConsumedProducts”表上)
4. 当前损坏(反射(reflect)在“DamagedProducts”表上)
5. 剩余余额=(上一个余额+今天交付的项目)-(当前消耗+当前损坏

|ID|ItemID|Item|Prev Bal|Current Delivered|Current Consumed|Current Damaged|Balance| --------------------------------------------------------------------------------------

我之前曾在这个论坛上问过如何以某种方式对表格求和，感谢这个社区的帮助，我得到了很好的结果，但我最近遇到了如上所述的问题。

Select I.ID, I.Item,
                    (Select IFNULL(SUM( (SELECT Quantity FROM inventory WHERE DATE(ItemTransactionDate) < CURDATE() GROUP BY I.ItemID)),0) - 
                    IFNULL((SELECT Quantity FROM inventory WHERE DATE(ItemTransactionDate) < CURDATE() GROUP BY I.ItemID),0) - 
                    IFNULL((SELECT Quantity FROM consumeditemmonitoring WHERE DATE(TransactionDate) < CURDATE() GROUP BY I.ItemID),0) As NEWBALANCE From inventory INV 
                    LEFT OUTER JOIN ( Select ItemID,IFNULL(SUM(Quantity),0) As Quantity from damagedinventory group by ItemID) DMG On INV.ItemID=DMG.ItemID 
                    LEFT OUTER JOIN (Select ItemID,IFNULL(SUM(Quantity),0) As Quantity from consumeditemmonitoring group by ItemID) CSMD On INV.ItemID=CSMD.ItemID GROUP BY I.ItemID),

                    (SELECT SUM(Quantity) As CurrentDeliveries FROM inventory WHERE DATE(ItemTransactionDate) = CURDATE() GROUP BY I.ItemID),

                    IFNULL(D.Quantity,0) As damagedQTY,

                    IFNULL(C.Quantity,0) As ConsumedQTY,

                    IFNULL(SUM(I.Quantity),0) - IFNULL(D.Quantity,0) - IFNULL(C.Quantity,0) As NEWBALANCE From inventory I LEFT OUTER JOIN 
                    (Select ItemID,IFNULL(SUM(Quantity),0) As Quantity from damagedinventory group by ItemID) D On I.ItemID=D.ItemID 
                    LEFT OUTER JOIN (Select ItemID,IFNULL(SUM(Quantity),0) As Quantity from consumeditemmonitoring group by ItemID) C On I.ItemID=C.ItemID GROUP BY I.ItemID"

我是编程新手，所以我真的没有足够的知识。非常感谢任何帮助。

Answer 1

希望这行得通，或者您可以调整它。我使用了滞后功能。这会向您显示该列的先前值(另请阅读有关 lead 函数的信息 here )

SQL 查询:

SELECT I.ID,
       I.ItemID,
       PREV_BAL,
       ITEMS_DELIVERED_TODAY,
       CURRENT_CONSUMED,
       CURRENT_DAMAGED,
       PREV_BAL + ITEMS_DELIVERED_TODAY - CURRENT_CONSUMED - CURRENT_DAMAGED
           AS REMANING_BALANCE
  FROM (SELECT I.ID,
               I.ItemID,
               (  LAG (
                      I.Quantity,
                      1)
                  OVER (PARTITION BY I.ItemID
                        ORDER BY TRUNC (TransactionDate))
                - D.prevQuantity
                - C.prevQuantity)
                   PREV_BAL,
               I.Quantity ITEMS_DELIVERED_TODAY,
               C.Quantity CURRENT_CONSUMED,
               D.Quantity CURRENT_DAMAGED
          FROM inventory  I
               LEFT OUTER JOIN
               (  SELECT ItemID,
                         IFNULL (SUM (Quantity), 0)   AS Quantity,
                         IFNULL (SUM (prevQuantity), 0) AS prevQuantity
                    FROM (SELECT ItemID,
                                 Quantity,
                                 LAG (
                                     Quantity,
                                     1)
                                 OVER (PARTITION BY ItemID
                                       ORDER BY TRUNC (TransactionDate))
                                     prevQuantity
                            FROM damagedinventory) damagedinventory
                GROUP BY ItemID) D
                   ON I.ItemID = D.ItemID
               LEFT OUTER JOIN
               (  SELECT ItemID,
                         IFNULL (SUM (Quantity), 0)   AS Quantity,
                         IFNULL (SUM (prevQuantity), 0) AS prevQuantity
                    FROM (SELECT ItemID,
                                 Quantity,
                                 LAG (
                                     Quantity,
                                     1)
                                 OVER (PARTITION BY ItemID
                                       ORDER BY TRUNC (TransactionDate))
                                     prevQuantity
                            FROM consumeditemmonitoring) consumeditemmonitoring
                GROUP BY ItemID) C
                   ON I.ItemID = C.ItemID)