一、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 b.parent_lineitem_id=a.lineitem_id
SET
a.haveoutproductcount=IFNULL(b.qty_ck,0)-IFNULL(c.qty_th,0),
a.balanceoutproductcount=IFNULL(a.quantity,0)-IFNULL(b.qty_ck,0)+IFNULL(c.qty_th,0),
a.haveoutproductamount=IFNULL(b.amount_ck,0)-IFNULL(c.amount_th,0),
a.balanceoutproductamount=IFNULL(a.tax_amount,0)-IFNULL(b.amount_ck,0)+IFNULL(c.amount_th,0)