MySQL算术运算中的问题

本文介绍了MySQL算术运算中的问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的查询有问题... 该查询不会求和所有(qty)...即使它有一个值，它也会输出"0"值. 这是查询:

I have a problem in my query... The query does not sum all the (qty)...even it have a value it outputs "0" value. Heres the query:

SELECT `tblinventory`.`PK` AS `PK`, `tblinventory`.`prdct_ID` AS `ProductID`, `tblinventory`.`prdct_upc_barcode` AS `UPCBarcode`, `tblinventory`.`prdct_supp_ID` AS `SupplierID`, `tblinventory`.`prdct_supp` AS `SupplierName`, `tblinventory`.`prdct_ctgry_ID` AS `CategoryID`, `tblinventory`.`prdct_ctgry` AS `CategoryName`, `tblinventory`.`prdct_name` AS `ProductName`, `tblinventory`.`prdct_desc` AS `ProductDesc`, `tblinventory`.`lot` AS `ProductLot`, `tblinventory`.`unitcost` AS `UnitCost`, `tblinventory`.`packaging` AS `Package`, `tblinventory`.`exprtion_date` AS `Expiration`, `tblinventory`.`min_stck_level` AS `MinStckLvl`, `tblinventory`.`prdct_srp` AS `SRP`, `tblinventory`.`prdct_retail_price` AS `RP`, `tblinventory`.`qty` AS `QTY`, IFNULL((SELECT SUM(tblwarehouse_prodcts.prod_qty) FROM tblwarehouse_prodcts WHERE (tblwarehouse_prodcts.prod_id=tblinventory.prdct_ID)),0) as W_Entry, IFNULL((SELECT SUM(tblreplacement_prodcts.prod_qty) FROM tblreplacement_prodcts WHERE (tblreplacement_prodcts.prod_id=tblinventory.prdct_ID)),0) as W_Rplcmnt, IFNULL((SELECT SUM(tbladjustment_prodcts.prod_qty) FROM tbladjustment_prodcts WHERE (tbladjustment_prodcts.prod_id=tblinventory.prdct_ID)),0) as W_Adjstmnt, IFNULL((SELECT SUM(tbloutgoing_prodcts.prod_qty) FROM tbloutgoing_prodcts WHERE (tbloutgoing_prodcts.prod_id=tblinventory.prdct_ID)),0) as W_OTStck, ifnull(( (SELECT SUM(tblwarehouse_prodcts.prod_qty) FROM tblwarehouse_prodcts WHERE (tblwarehouse_prodcts.prod_id=tblinventory.prdct_ID)) + (SELECT SUM(tblreplacement_prodcts.prod_qty) FROM tblreplacement_prodcts WHERE (tblreplacement_prodcts.prod_id=tblinventory.prdct_ID)) - (SELECT SUM(tbladjustment_prodcts.prod_qty) FROM tbladjustment_prodcts WHERE (tbladjustment_prodcts.prod_id=tblinventory.prdct_ID)) - (SELECT SUM(tbloutgoing_prodcts.prod_qty) FROM tbloutgoing_prodcts WHERE (tbloutgoing_prodcts.prod_id=tblinventory.prdct_ID) )),0) AS TOTALQTY from tblinventory GROUP BY prdct_ID

....有人可以帮我吗?

....can anyone help me?

推荐答案

jleonorlane写道: jleonorlane wrote:

SELECT来自tblwarehouse_prodcts的SUM(tblwarehouse_prodcts.prod_qty)(tblwarehouse_prodcts.prod_id = tblinventory.prdct_ID)

SELECT SUM(tblwarehouse_prodcts.prod_qty) FROM tblwarehouse_prodcts WHERE (tblwarehouse_prodcts.prod_id=tblinventory.prdct_ID)

您是否尝试过在单独的窗口中执行它们并检查它们是否给出正确的结果? 看这里: IFNULL(expr1，expr2) [ ^ ]-默认结果IFNULL(expr1，expr2)的值是两个表达式中的通用"值. 解决问题是什么...从一个小的查询开始！

Have you tried executing them in a separate window and checked if they give correct results? Look here: IFNULL(expr1,expr2)[^] - The default result value of IFNULL(expr1,expr2) is the more "general" of the two expressions. Troubleshoot whats wrong... start with a small query!