如何在SQL的列值总和中添加条件? [英] How to add a condition in sum of column value of SQL ?

问题描述

大家好，

我有三个表:
OITM(ItemCode，名称)，
OITW(ItemCode，WareHouseCode，StockHand，OrderQty)，
WITM(ItemCode，LastUStock)

现在，我想加入这三个表，并要选择ItemCode和按ItemCode分组的sum(StockHand-OrderQty)的值，其中sum(StockHand-OrderQty)<> LastUStock.如何使用联接查询获取此值?

Hi all ,

I have three tables:
OITM (ItemCode,Name),
OITW (ItemCode,WareHouseCode,StockHand,OrderQty),
WITM (ItemCode,LastUStock)

Now I want to join the three tables and I want to select he values of ItemCode and sum(StockHand - OrderQty) grouped by ItemCode where the sum(StockHand - OrderQty) <> LastUStock. How to get this value using join query?

Select OITM.ItemCode,sum(StockHand - OrderQty) from OITM  inner join WITM on OITM.ItemCode= WITM.ItemCode inner join OITW on OITM.ItemCode =OITW.ItemCode and OITW.WareHouseCode in ('WH1','WH2','WH4')  Group by OITM.ItemCode Having sum(StockHand - OrderQty) <> WITM.LastUStock

请帮助任何人

谢谢
Surajit

please help any one

Thanks
Surajit

推荐答案

尝试修改查询.以下行不应该是联接结构本身的一部分，而应该是联接结构的过滤器:

Try modifying the query. The following line should not be a part of the join structure itself, but rather a filter for the join structure:

OITW.WareHouseCode in ('WH1','WH2','WH4')

将其作为WHERE子句放入:

Put it in as a WHERE clause:

SELECT
   OITM.ItemCode,
   sum(StockHand - OrderQty) AS NetStockHand
FROM
   OITM inner join 
   WITM on OITM.ItemCode = WITM.ItemCode inner join 
   OITW on OITM.ItemCode = OITW.ItemCode 
WHERE 
   OITW.WareHouseCode in ('WH1','WH2','WH4')
GROUP BY 
   OITM.ItemCode 
HAVING
   sum(StockHand - OrderQty) <> WITM.LastUStock

这篇关于如何在SQL的列值总和中添加条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！