如何使用SQL求和和减法? [英] How to SUM and SUBTRACT using SQL?

问题描述

我正在使用MySQL，并且有两个表:

I am using MySQL and I have two tables:

master_table

• ORDERNO
• ITEM
• 数量

stock_bal

• ITEM
• BAL_QTY

主表具有重复的ORDERNO和ITEM值.我已经使用SQL'GROUP BY'子句获得了QTY的总和.

Master table has duplicate ORDERNO and ITEM values. I have get total QTY using SQL 'GROUP BY' clause.

我需要从ITEM(master_table)的总和中减去/减去BAL_QTY.我已经使用查询获得了SUM QTY值(实际上有很多行).

I need to deduct/subtract BAL_QTY from SUM of ITEM (master_table). I've got SUM QTY value using query (actually there are many rows).

推荐答案

我认为这就是您想要的. NEW_BAL是从余额中减去的QTY s之和:

I think this is what you're looking for. NEW_BAL is the sum of QTYs subtracted from the balance:

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

如果要使用新余额更新项目余额，请使用以下命令:

If you want to update the item balance with the new balance, use the following:

UPDATE stock_bal
SET    BAL_QTY = BAL_QTY - (SELECT   SUM(QTY)
                            FROM     master_table
                            GROUP BY master_table.ORDERNO,
                                     master_table.ITEM)

这是假设您将减法向后发布；它会从天平中按顺序减去数量，这在不了解表格的情况下最有意义.如果我错了，只需交换这两个即可更改它:

This assumes you posted the subtraction backward; it subtracts the quantities in the order from the balance, which makes the most sense without knowing more about your tables. Just swap those two to change it if I was wrong:

(SUM(master_table.QTY) - stock_bal.BAL_QTY) AS NEW_BAL

这篇关于如何使用SQL求和和减法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！