我认为这是你正在寻找的. NEW_BAL是余额中扣除的数量之和:
SELECT master_table.ORDERNO,
master_table.ITEM,
SUM(master_table.QTY),
stock_bal.BAL_QTY,
(stock_bal.BAL_QTY - SUM(master_table.QTY)) AS NEW_BAL
FROM master_table INNER JOIN
stock_bal ON master_bal.ITEM = stock_bal.ITEM
GROUP BY master_table.ORDERNO,
master_table.ITEM
如果要使用新余额更新物品余额,请使用以下内容:
UPDATE stock_bal
SET BAL_QTY = BAL_QTY - (SELECT SUM(QTY)
FROM master_table
GROUP BY master_table.ORDERNO,
master_table.ITEM)
这假设你向后张贴减法;它从天平中减去订单中的数量,这在没有更多地了解你的桌子的情况下是最有意义的.如果我错了,只要互换这两个来改变它:
(SUM(master_table.QTY) - stock_bal.BAL_QTY) AS NEW_BAL