视图代码(mysql)有什么问题 [英] What is the problem in view code(mysql)
本文介绍了视图代码(mysql)有什么问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我用这个代码创建了一个Mysql View,它总结了商品库存..
i use this code to create Mysql View that sum the items stock ..
CREATE OR REPLACE VIEW `view_items_defin` as
SELECT items.stitems_ID,
items.stitems_Status,
items.stitems_Name,
items.stitems_Pharma_ActiveIngredient,
items.stitems_Code,
manuf.manu_Name,
COALESCE(ca.Amount, 0) + COALESCE(pa.Amount,0) AS Stock
FROM st_items items
LEFT JOIN (SELECT sns.stitems_ID,
SUM(sns.StockQnty) AS Amount
FROM stock_noserials sns
GROUP BY sns.stitems_ID) ca
ON ca.stitems_ID = items.stitems_ID
LEFT JOIN (SELECT pis.stitems_ID,
COUNT(*) AS Amount
FROM purchases_item_seriels pis
WHERE pis.pis_Statues IN (0, 5, 6)
GROUP BY pis.stitems_ID) pa
ON pa.stitems_ID = items.stitems_ID
INNER JOIN `st_plug_manufacturer` AS manuf
ON items.`stitems_Manufacturer` = manuf.`manu_ID`;
这是表数据:
1- stock_noserials表
this are tables data :
1- stock_noserials table
---------------------------------
id | stitems_ID | StockQnty | ---
1 | 15078 | -6 | ---
2 | 15078 | 0 | ---
3 | 15078 | 0 | ---
---------------------------------
且purchase_item_seriels表中没有数据
但是查看代码的结果:Stock = -2 not -6
我尝试了什么:
i试图跟随很多人项目,但相同的问题..
and there is no data in purchases_item_seriels table
but the result of the view code :Stock = -2 not -6
What I have tried:
i tried to follow many items but the same problem ..
推荐答案
尝试将以下行更改为其他内容:
Try changing the following line to something else :
...
COUNT(*) AS Amount
...
这篇关于视图代码(mysql)有什么问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文