MySQL算术运算中的问题 [英] Problem in MySQL Arithmethic Operations

问题描述

我的查询有问题...
该查询不会求和所有(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) [
解决问题是什么...从一个小的查询开始！

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!

这篇关于MySQL算术运算中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！