我不确定你到底想要什么，但我想大概是这样的：

SELECT `Item`, `qty`-`BAL_QTY` as `qty` FROM ((SELECT Item, SUM(`QTY`) as qty FROM `master_table` GROUP BY `ITEM`) as A NATURAL JOIN `stock_table`) as B

我想这就是你要找的。 NEW_BAL 是从余额中减去 QTY 的和：

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

啊作业。

因此，等等，您需要从已订购的项目总数中扣除库存项目的余额？我必须告诉你，这听起来有点倒退。一般来说，我认为人们会以相反的方式做这件事。从余额中扣除订购的商品总数。

如果你真的需要这样做的话...假设该项在stock_bal中是唯一的...

SELECT s.ITEM, SUM(m.QTY) - s.QTY AS result
FROM stock_bal s
INNER JOIN master_table m ON m.ITEM = s.ITEM
GROUP BY s.ITEM, s.QTY

我已经尝试过这种技术。将从数据中减去的值乘以(-1)，然后求和()，得到减去的量。

-- Loan Outstanding
    select  'Loan Outstanding' as Particular, sum(Unit), sum(UptoLastYear), sum(ThisYear), sum(UptoThisYear)
        select 
            sum(laod.dr) as Unit,
            sum(if(lao.created_at <= '2014-01-01',laod.dr,0)) as UptoLastYear,
            sum(if(lao.created_at between '2014-01-01' and '2015-07-14',laod.dr,0)) as ThisYear,
            sum(if(lao.created_at <= '2015-07-14',laod.dr,0)) as UptoThisYear
        from loan_account_opening as lao
        inner join loan_account_opening_detail as laod on lao.id=laod.loan_account_opening_id
        where lao.organization = 3
        union
        select
            sum(lr.installment)*-1 as Unit,
            sum(if(lr.created_at <= '2014-01-01',lr.installment,0))*-1 as UptoLastYear,
            sum(if(lr.created_at between '2014-01-01' and '2015-07-14',lr.installment,0))*-1 as ThisYear,
            sum(if(lr.created_at <= '2015-07-14',lr.installment,0))*-1 as UptoThisYear
        from loan_recovery as lr
        inner join loan_account_opening as lo on lr.loan_account_opening_id=lo.id
        where lo.organization = 3