php - mysql：基于一张表对其他连接表值求和

Question

我的查询是计算每个产品的数量不足的总数。

要创建一个产品，我需要多个项目。所以，我需要检查每个项目是否不足。然后，向用户显示每个项目的不足数量。

在bpo_require_item表中，每个项目都有必要的数量来创建一个产品。如果产品订单多于一个，则同一项目存在多于一行。（model_id指产品。）

id | model_id | item_id | item_code | necessary_qty |
1       1           1         I001         2000
2       1           2         I002         2000
3       1           3         I003         1000              
4       2           1         I001         1500
5       2           2         I002         4500
6       2           3         I003         1500  
7       3           1         I001         2500
8       3           2         I002         3750
9       3           3         I003         5000  
10      3           5         I005         6250 

在warehouse_item表中，实际仓库中的每个项目都有剩余数量。

id | item_id | item_code | normal_quantity |
 1       1         I001         140
 2       2         I002         120
 3       3         I003         110
 4       5         I005         200

每个项目都有，safety stock quantity因为如果仓库内的项目几乎达到安全库存，那么我们需要从我们的供应商那里订购这个项目。这是item桌子。

item_id | item_code | safety_stock_qty |
1         I001         100
2         I002         100
3         I003         100
5         I005         100

在pre_supplier_order表格中，我们保存了每个需要的项目数量（但这不是真正的订单）。

id | item_id | item_code | order_qty |
 1       1         I001         3460
 2       2         I002         6480
 3       3         I003         2490

在supplier_order_item表中，这是真实的订单数据。

id | item_id | item_code | order_qty | date
 1       1         I001         1000     2018-09-25
 2       1         I001         500      2018-09-27

注意：还有一个类似buyer_orderand的表，但这里我不具体提及，supplier_order因为我只需要检查 bpo_status='O4'and supplier_order。spo_status=S1

现在，我需要根据bpo_require_item表格计算每个项目的不足数量。

首先，将所有相同的 item_id 数量bpo_require_item相加，然后将pre_supplier_order表和supplier_order_item表中所有相同的 item_id 相加。然后我想用以下公式计算每个项目：

each item insufficient_qty = ( (warehouse.normal_quantity - item. safety_stock_qty) + pre_supplier_order.order_qty + supplier_order_item.quantity ) - (bpo_require_item.necessary_qty)

对于上表数据，我对每个项目数量不足的预期结果如下：

item_id ( necessary_qty ) - ((warehouse-safety_stock_qty) + pre_supplier_order + supplier_order_item)) = insufficient_qty
   1        6000                    40                        3460                 1500                    -1000
   2        10250                   20                        6480                                         -3750
   3        7500                    10                        2490                                         -5000
   5        6250                    100                                                                    -6150

但是，我的查询得到了错误的结果。

SELECT *
    FROM (    
    SELECT 
          bri.item_id, bri.item_code, bri.item_name,
          bri.vmc_code, bri.hs_code, itm.safety_stock_qty,
          SUM(bri.necessary_qty) as total_require_item,
          SUM(pre.order_qty) as total_pre_item, 
          SUM(soi.quantity) as total_supplier_order,
          wi.normal_quantity, 
       (
         ( 
           (wi.normal_quantity-itm.safety_stock_qty) + 
               coalesce(SUM(pre.order_qty),0)+
               coalesce(soi.quantity,0) 
              ) - coalesce(SUM(bri.necessary_qty),0)
           ) as insufficient_qty
        FROM `buyer_order` as bo
        LEFT JOIN bpo_require_item as bri ON (bo.po_no = bri.po_no AND bo.bpo_status='O4')
        LEFT JOIN item as itm ON (bri.item_id = itm.item_id AND itm.flag=1)
        LEFT JOIN warehouse_item as wi ON (bri.item_id = wi.item_id)
        LEFT JOIN pre_supplier_order as pre ON (bri.item_id = pre.item_id)
        LEFT JOIN supplier_order_item as soi ON (bri.item_id = soi.item_id)
        LEFT JOIN supplier_order as so ON (soi.supplier_order_id = so.supplier_order_id AND so.spo_status='S1')
     GROUP BY bri.item_id
     ORDER BY bo.po_no, bri.item_id 
    ) as requireItemTable 
    WHERE insufficient_qty < 0 

我认为问题在于SUM每个项目LEFT JOIN都是错误的。但我不确定如何解决它。

标签： phpmysql