在多个where子句中计算mysql

Question

我有如图所示的数据库结构(它被简化了)

​

因此，现在我想计算所有库存中的产品，结果如下所示(假设store表有两条记录store1和store2)：

​

​

如果这是不可能的，这种格式也可以接受

​

​

主要的问题是，我不知道如何计算每个商店的当前库存。

the curent stock for each stock =  sum of all productin this stock
                                 - sum of all productout this stock
                                 + sum of all producttransfer with transfertype=0 in this stock 
                                 - sum of all producttransfer with transfertype=1 in this stock

那么，当门店编号不固定时，我应该如何计算每个门店的门店数量呢？

Answer 1

我认为它应该可以工作，我是在路上写的，所以不能100%确定，而且可能会有一些打字错误

    select A1.ProductID, A1.storehouseID, (SUM(IN)-SUM(OUT)) as quantity
    from (
          Select AD.productID, A.storehouseID, SUM(AD.Quantity) as IN
          from actions A inner join productIN PI on A.ID = PI.ID
                         inner join actiondetails AD on PI.ID = AD.ID
    group by AD.productID, A.storehouseID

    UNION

           select AD.ProductID, A.storehouseID, SUM(AD.Quantity)
           from actions A inner join ProductTransfers T on A.ID = T.ID
                          inner join actiondetails AD on A.ID = AD.ID
           where transferType = 0
           group by AD.ProductID, A.storehouseID) as A1 

                                                        inner join 

          (select AD.productID, A.storehouseID, SUM(AD.Quantity) as Out
           from actions A inner join ProductOut PO on A.ID = PO.ID
                          inner join actiondetails AD on AD.ID = A.ID
           group by AD.ProductID, A.storehouseID

     UNION

           select AD.productID, A.storehouse.ID, SUM(AD.Quantity) as Out
           from actions A inner join ProductTransfers T on A.ID = T.ID
                          inner join actiondetails AD on AD.ID = A.ID
           where TransferType = 1
           group by AD.ProductID, A.storehouseID) as A2  
           on A1.ProductID = A2.ProductID and A1.storehouseID = A2.storehouseID
     group by A1.ProductID, A1.StorehouseID