使用加入的两个表的总和 [英] Sum Of Two Tables Using Join
本文介绍了使用加入的两个表的总和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两张桌子INStock和OUTStock。使用Join和Getting Current Stock合并这两个表..我已经完成了这样的查询..
SELECT DISTINCT
(INStock.WareHouse) AS WH,INStock.ItemCode,
SUM(INStock.Qty) AS INQty,SUM(OUTStock.Qty) AS OUTQty,
SUM(INStock.Qty) - SUM(OUTStock.Qty) as 股票
FROM INStock 左 JOIN
OUTStock ON INStock.ItemCode = OUTStock.ItemCode
group by INStock.WareHouse,INStock.ItemCode
拥有 INStock.ItemCode = ' 0001'
但我无法获得如下所示的正确结果..
INSTOCK表
------------------------ -
WareHouse |代码|数量
-------------------------
1 | 0001 | 5
1 | 5555 | 4
2 | 0001 | 6
3 | 0001 | 2
3 | 4444 | 2
-------------------------
OUTSTOCK表
-------- ------------------
WareHouse |代码|数量
-------------------------
1 | 0001 | 3
1 | 5555 | 2
2 | 0001 | 3
2 | 4444 | 1
-------------------------
我希望看起来像这样,(特殊代码'0001')
--------- ----------------------------------
WareHouse |代码| INQty | OutQty |股票
-------------------------------------------
1 | 0001 | 5 | 3 | 2
2 | 0001 | 6 | 3 | 3
3 | 0001 | 2 | 0 | 2
------------------------------------------
请建议..
解决方案
试试这个:
DECLARE @ INSTOCK TABLE (WareHouse INT ,代码 VARCHAR ( 30 ),数量 INT )
INSERT INTO @ INSTOCK (WareHouse,Code,Qty)
SELECT 1 ,' 0001', 5
UNION ALL SELECT 1 , ' 5555', 4
UNION ALL SELECT 2 ,' 0001', 6
UNION ALL SELECT 3 ,' 0001 ', 2
UNION ALL SELECT 3 ,' 4444', 2
DECLARE @ OUTSTOCK 表(WareHouse INT ,代码 VARCHAR ( 30 ),数量 INT )
INSERT INTO @ OUTSTOCK (WareHouse,Code,Qty)
SELECT 1 ,' 0001', 3
UNION ALL SELECT 1 ,' 5555', 2
UNION ALL SELECT 2 ,' 0001', 3
UNION ALL SELECT 2 ,' 4444', 1
SELECT t1.WareHouse,t1.Code, t1.Qty AS INQty, COALESCE (t2.Qty, 0 ) AS OUTQty,t1.Qty - COALESCE (t2.Qty, 0 ) AS 股票
FROM @ INSTOCK AS t1 LEFT JOIN @ OUTSTOCK AS t2 ON t1.WareHouse = t2 .WareHouse AND t1.Code = t2.Code
WHERE t1.Code = ' 0001'
结果:
1 0001 5 3 2
2 0001 6 3 3
3 0001 2 0 2
试试这个
创建 表 #INStock(WareHouse NVARCHAR ( 50 ),ItemCode NVARCHAR ( 50 ),数量 INT )
CREATE TABLE #OUTStock(WareHouse NVARCHAR ( 50 ),ItemCode NVARCHAR ( 50 ),数量 INT )
INSERT INTO #INSTOCK(WAREHOUSE,ITEMCODE,QTY) VALUES (' 1',' 0001',< span class =code-digit> 5 )
INSERT INTO # INSTOCK(WAREHOUSE,ITEMCODE,QTY) VALUES (' 1 ',' 5555', 4 )
INSERT INTO #INSTOCK(WAREHOUSE,ITEMCODE,QTY) VALUES (' 2', ' 0001', 6 )
INSERT INTO #INSTOCK(WAREHOUSE,ITEMCODE,QTY) VALUES (' 3',' 0001', 2 )
INSERT INTO #INSTOCK(WAREHOUSE,ITEMCODE,QTY) VALUES (' 3',' 0004', 2 )
INSERT INTO #OUTStock(WAREHOUSE,ITEMCODE,QTY)VALUES (' 1',' 0001', 3 )
< span class =code-keyword> INSERT INTO #OUTStock(WAREHOUSE,ITEMCODE,QTY) VALUES (' 1',' 5555', 2 )
INSERT INTO #OUTStock(WAREHOUSE,ITEMCODE,QTY) VALUES (' 2', 0001', 3 )
INSERT INTO #OUTStock(仓库, ITEMCODE,QTY) VALUES (' 2',' 4444', 1 )
- SELECT * FROM #INSTOCK
SELECT
(INStock.WareHouse) AS WH,INStock.ItemCode
,
SUM(ISNULL(INStock.Qty, 0 )) AS INQty,
SUM(ISNULL(OUTStock.Qty, 0 )) AS OUTQty,
SUM(ISNULL( INStock.Qty, 0 )) - SUM(ISNULL(OUTStock.Qty, 0 )) as 股票
FROM #INStock INSto ck LEFT JOIN
#OUTStock OUTStock
ON INStock.ItemCode = OUTStock.ItemCode
AND INStock.WareHouse = OUTStock.WareHouse
group by INStock.WareHouse,INStock.ItemCode
INStock.ItemCode = ' 0001'
order by INStock.WareHouse
drop table #INStock
drop table #OUTStock
试试这个
选择 I.WareHouse,I.Code ,ISNULL(I.Qty, 0 )InQty,ISNULL(O.Qty, 0 )OutQty,( ISNULL(I.Qty, 0 ) - ISNULL(O.Qty, 0 ))股票
来自 INStock I( nolock )
left join
OUTStock O( nolock )
> I.warehouse = O.warehouse
和 I.code = O.Code
Hi,
I Have Two Tables INStock and OUTStock. Merging these two Tables using Join and Getting Current Stock..I've done a query like this..
SELECT DISTINCT
(INStock.WareHouse) AS WH,INStock.ItemCode,
SUM(INStock.Qty) AS INQty,SUM(OUTStock.Qty) AS OUTQty,
SUM(INStock.Qty) - SUM(OUTStock.Qty) as Stock
FROM INStock Left JOIN
OUTStock ON INStock.ItemCode = OUTStock.ItemCode
group by INStock.WareHouse,INStock.ItemCode
Having INStock.ItemCode='0001'
But i Could not Get Correct Result Like Below..
INSTOCK Table
-------------------------
WareHouse | Code | Qty
-------------------------
1 | 0001 | 5
1 | 5555 | 4
2 | 0001 | 6
3 | 0001 | 2
3 | 4444 | 2
-------------------------
OUTSTOCK Table
--------------------------
WareHouse | Code | Qty
-------------------------
1 | 0001 | 3
1 | 5555 | 2
2 | 0001 | 3
2 | 4444 | 1
-------------------------
I want to Look like this,(For Particular Code '0001')
-------------------------------------------
WareHouse | Code | INQty | OutQty | Stock
-------------------------------------------
1 | 0001 | 5 | 3 | 2
2 | 0001 | 6 | 3 | 3
3 | 0001 | 2 | 0 | 2
------------------------------------------
Please Suggest..
解决方案
Try this:
DECLARE @INSTOCK TABLE (WareHouse INT, Code VARCHAR(30), Qty INT) INSERT INTO @INSTOCK (WareHouse, Code, Qty) SELECT 1, '0001', 5 UNION ALL SELECT 1, '5555', 4 UNION ALL SELECT 2, '0001', 6 UNION ALL SELECT 3, '0001', 2 UNION ALL SELECT 3, '4444', 2 DECLARE @OUTSTOCK TABLE (WareHouse INT, Code VARCHAR(30), Qty INT) INSERT INTO @OUTSTOCK (WareHouse, Code, Qty) SELECT 1, '0001', 3 UNION ALL SELECT 1, '5555', 2 UNION ALL SELECT 2, '0001', 3 UNION ALL SELECT 2, '4444', 1 SELECT t1.WareHouse, t1.Code, t1.Qty AS INQty, COALESCE(t2.Qty,0) AS OUTQty, t1.Qty - COALESCE(t2.Qty,0) AS Stock FROM @INSTOCK AS t1 LEFT JOIN @OUTSTOCK AS t2 ON t1.WareHouse = t2.WareHouse AND t1.Code = t2.Code WHERE t1.Code = '0001'
Result:
1 0001 5 3 2 2 0001 6 3 3 3 0001 2 0 2
Try this as well
CREATE TABLE #INStock (WareHouse NVARCHAR(50), ItemCode NVARCHAR(50),Qty INT) CREATE TABLE #OUTStock ( WareHouse NVARCHAR(50),ItemCode NVARCHAR(50),Qty INT) INSERT INTO #INSTOCK (WAREHOUSE,ITEMCODE,QTY)VALUES ('1','0001',5) INSERT INTO #INSTOCK (WAREHOUSE,ITEMCODE,QTY)VALUES ('1','5555',4) INSERT INTO #INSTOCK (WAREHOUSE,ITEMCODE,QTY)VALUES ('2','0001',6) INSERT INTO #INSTOCK (WAREHOUSE,ITEMCODE,QTY)VALUES ('3','0001',2) INSERT INTO #INSTOCK (WAREHOUSE,ITEMCODE,QTY)VALUES ('3','0004',2) INSERT INTO #OUTStock (WAREHOUSE,ITEMCODE,QTY)VALUES ('1','0001',3) INSERT INTO #OUTStock (WAREHOUSE,ITEMCODE,QTY)VALUES ('1','5555',2) INSERT INTO #OUTStock (WAREHOUSE,ITEMCODE,QTY)VALUES ('2','0001',3) INSERT INTO #OUTStock (WAREHOUSE,ITEMCODE,QTY)VALUES ('2','4444',1) --SELECT * FROM #INSTOCK SELECT (INStock.WareHouse) AS WH,INStock.ItemCode , SUM(ISNULL(INStock.Qty,0)) AS INQty, SUM(ISNULL(OUTStock.Qty,0)) AS OUTQty , SUM(ISNULL(INStock.Qty,0)) - SUM(ISNULL(OUTStock.Qty,0)) as Stock FROM #INStock INStock LEFT JOIN #OUTStock OUTStock ON INStock.ItemCode = OUTStock.ItemCode AND INStock.WareHouse = OUTStock.WareHouse group by INStock.WareHouse,INStock.ItemCode Having INStock.ItemCode='0001' order by INStock.WareHouse drop table #INStock drop table #OUTStock
Hi,
try this
select I.WareHouse,I.Code,ISNULL(I.Qty,0) InQty,ISNULL(O.Qty,0) OutQty , (ISNULL(I.Qty,0)-ISNULL(O.Qty,0)) Stock from INStock I(nolock) left join OUTStock O (nolock) on I.warehouse=O.warehouse and I.code=O.Code
这篇关于使用加入的两个表的总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文