SQL子查询（库存数量运行时通过总计库存和库存分组，然后从库存中减去库存）

I have 2 tables stockin and stockout i want to calculate stockin quantity runtime by sum up the stock in and stock out group by store and than subtract from stockin to stock out .. My query is working well but when it doesnt found any record in stock out table it do some unusual calculation

我有2个表stockin和stockout我想计算库存数量运行时通过总结库存和库存逐个组,然后从stockin减去库存..我的查询工作正常,但它没有找到任何库存记录在表中它做了一些不寻常的计算

Select 
CASE 
    WHEN 
    (select ISNULL(Items_store.Item_ID,0) from Items_Store where Items_Store.Store_ID = Inventory_Incoming.Store_ID)
    <> 0
    THEN   
    SUM(Quentity)- 
    (select SUM(Items_Out) from Items_Store where Items_Store.Store_ID = Inventory_Incoming.Store_ID)
    ELSE
    SUM(Quentity) 
END as Stock 
,Store_ID,Item_ID
from Inventory_Incoming 
where Item_ID =1

group by 
Store_ID,
Item_ID

2 个解决方案

#1

This

(select ISNULL(Items_store.Item_ID,0) from Items_Store where ...)

gets you the Item_ID from Items_Store. In case it is null (and I suppose this is never the case) you replace it by 0. In case no record can be found, you get NULL.

从Items_Store获取Item_ID。如果它为null(我想这绝不是这种情况)你将它替换为0.如果没有找到记录,你得到NULL。

Replace this by

替换它

ISNULL((select Items_store.Item_ID from Items_Store where ...), 0)

But as Gordon already mentioned, better simplify your query.

但正如戈登已经提到的那样,更好地简化您的查询。

EDIT: It seems you are not using all criteria when linking the tables. See here:

编辑:链接表时似乎没有使用所有条件。看这里:

select 
  sum(quentity) - 
  coalesce(
  (
    select sum(items_out) 
    from items_store is
    where is.store_id = ii.store_id and is.item_id = ii.item_id
  ), 0)
from inventory_incoming ii
where item_id =1
group by store_id, item_id;

items_store must be linked by both store_id and item_id.

items_store必须由store_id和item_id链接。

#2

You can greatly simplify your query just by doing:

您只需执行以下操作即可大大简化查询:

Select (sum(Quentity) - 
        coalesce((select SUM(Items_Out)
                  from Items_Store s
                  where s.Store_ID = ii.Store_ID
                 ), 0)
        ) as Stock, Store_ID, Item_ID
from Inventory_Incoming ii
where ii.Item_ID = 1
group by ii.Store_ID, ii.Item_ID;

Perhaps this will fix your problem.

也许这会解决你的问题。

#1