一、demo脚本
UPDATE ld_salesorderdetail a
LEFT JOIN (
SELECT
b.parent_lineitem_id,
SUM(b.quantity) AS qty_ck,
SUM(b.tax_amount) AS amount_ck
FROM ld_outboundorder a
LEFT JOIN ld_outboundorderdetail b ON b.id=a.out_id
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND (CASE WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=48 AND deploy_value=0) THEN a.approvestatus='已批准' ELSE 1=1 END)
AND b.entitytype='SalesOrder'
AND b.entitytypeid>0
GROUP BY b.parent_lineitem_id
) b ON b.parent_lineitem_id=a.lineitem_id
LEFT JOIN (
SELECT
b.parent_lineitem_id,
SUM(b.quantity) AS qty_th,
SUM(b.tax_amount) AS amount_th
FROM ld_salesback a
LEFT JOIN ld_salesbackdetail b ON b.id=a.salesbackid
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND a.salesbackstatus='已入库'
AND b.entitytype='SalesOrder'
AND b.entitytypeid>0
GROUP BY b.parent_lineitem_id
) c ON c.parent_lineitem_id=a.lineitem_id
SET
a.haveoutproductcount=IFNULL(b.qty_ck,0)-IFNULL(c.qty_th,0),
a.haveoutproductamount=IFNULL(b.amount_ck,0)-IFNULL(c.amount_th,0)
WHERE FIND_IN_SET(a.id,@source_list);
UPDATE ld_purchaseorderdetail a
LEFT JOIN (
SELECT
b.parent_lineitem_id,
SUM(b.quantity) AS qty_rk
FROM ld_stock a
LEFT JOIN ld_stockdetail b ON b.id=a.stock_id
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND (CASE WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=48 AND deploy_value=0) THEN a.approvestatus='已批准' ELSE 1=1 END)
AND b.entitytype='PurchaseOrder'
AND b.entitytypeid>0
GROUP BY b.parent_lineitem_id
) b ON b.parent_lineitem_id=a.lineitem_id
LEFT JOIN (
SELECT
b.parent_lineitem_id,
SUM(b.quantity) AS qty_th
FROM ld_purchaseback a
LEFT JOIN ld_purchasebackdetail b ON b.id=a.purchasebackid
WHERE a.deleted=0
-- AND a.approvestatus<>'已拒绝'
AND a.purchasebackstatus='已出库'
AND b.entitytype='PurchaseOrder'
AND b.entitytypeid>0
GROUP BY b.parent_lineitem_id
) c ON c.parent_lineitem_id=a.lineitem_id
SET
a.havepurchasecount=IFNULL(b.qty_rk,0)-IFNULL(c.qty_th,0)
WHERE FIND_IN_SET(a.id,@source_list);