视图代码（mysql）有什么问题 [英] What is the problem in view code(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屋！