mysql - 在mysql查询中添加和减去总和列

Question

我有这个 SQL 来检索所有项目

SELECT A.item_code,A.description,A.uom, A.open_stock, B.recd_total, C.issue_qty
FROM chem_master as A
LEFT JOIN ( SELECT item_code, sum(recd_qty) as recd_total
from chem_reciepts GROUP BY item_code) as B on A.item_code=B.item_code
LEFT JOIN(SELECT item_code, sum(iss_qty) as issue_qty
from chem_issue GROUP BY item_code) as C on A.item_code=C.item_codeand 

返回的数据为

Item_code Description                      unit op.stock recd_total issue_qty

25139     ALUMINIUM OXIDE ACTIVE NEUTRAL   GM       0         0          0
54006     L-ASCORBIC ACID                  GM       0         0          0
AC001     ACETIC ACID GLACIAL              LTR      0         14         2
AC002     ACETONE AR                       LTR      0         0          0
AC005     ACACIA POWDER                    GM       0         0          0

我只想要那些open_stock + recd_total - issues_qty = 0的记录

如何修改sql？

Answer 1

您可以添加 having 以及您在查询中提到的条件(在查询末尾)。
如下所示:

HAVING op.stock+recd_total-issue_qty=0