求一个结存逻辑SQL

[weisai]

期初

仓库 产品 数量
A P001 100
A P002 200
B P001 120

发出
仓库 产品 发出数量
A P001 50
B P001 30

收入
仓库 产品 发出数量
A P001 10
A P002 20
C P001 15
C P003 10

要求结果

仓库 产品 期初 发出 收入 结存
A P001 100 50 10 60
A P002 200 0 20 220
B P001 120 30 0 90
C P001 0 0 15 15
C P003 0 0 10 10

[Hello World,]

DECLARE @期初 TABLE(仓库 NVARCHAR(10) NOT NULL,产品 NVARCHAR(10) NOT NULL,数量 INT NOT NULL) 
INSERT @期初( 仓库, 产品, 数量)
VALUES('A','P001',100),('A','P002', 200),('B','P001', 120 )

DECLARE @发出 TABLE(仓库 NVARCHAR(10) NOT NULL, 产品 NVARCHAR(10) NOT NULL, 发出数量 INT NOT NULL)
INSERT @发出    (        仓库, 产品, 发出数量    )
VALUES('A','P001',50 ),('B','P001',30 )

DECLARE @收入  TABLE(仓库 NVARCHAR(10) NOT NULL, 产品 NVARCHAR(10) NOT NULL,  发出数量 INT NOT NULL)
INSERT @收入(仓库, 产品, 发出数量)
VALUES('A','P001',10),('A','P002',20),
('C','P001', 15	 ),('C','P003',10 )

--要求结果

--仓库        产品             期初              发出             收入            结存
--A           P001             100                 50              10                 60
--A           P002              200                0                20                 220
--B           P001              120                 30                0                 90
--C           P001                0                   0                 15               15
--C           P003               0                    0                  10               10 

SELECT p.仓库, p.产品, COALESCE(p.期初,0) 期初,  COALESCE(p.发出,0)*-1 发出, 
COALESCE(p.收入,0) 收入, COALESCE(p.期初,0)+COALESCE(p.发出,0)+COALESCE(p.收入,0) 结存 FROM (
SELECT 仓库, 产品, 数量,'期初' AS 类别 FROM @期初
UNION ALL SELECT 仓库, 产品, -发出数量, '发出' FROM @发出
UNION ALL SELECT 仓库, 产品, 发出数量,'收入' FROM @收入) list PIVOT (SUM(数量) FOR 类别 IN (期初,发出,收入)) p
ORDER BY p.仓库,p.产品

[RINK_1]

IF OBJECT_ID(N'TEMPDB.DBO.#T1') IS NOT NULL
DROP TABLE #T1
GO

CREATE TABLE #T1
(WAREHOUSE VARCHAR(10),
 ITEM VARCHAR(10),
 QTY INT)

INSERT INTO #T1
SELECT 'A','P001',100 UNION ALL
SELECT 'A','P002',200 UNION ALL
SELECT 'B','P001',120 


IF OBJECT_ID(N'TEMPDB.DBO.#T2') IS NOT NULL
DROP TABLE #T2
GO

CREATE TABLE #T2
(WAREHOUSE VARCHAR(10),
 ITEM VARCHAR(10),
 QTY INT)

INSERT INTO #T2
SELECT 'A','P001',50 UNION ALL
SELECT 'B','P001',30 

IF OBJECT_ID(N'TEMPDB.DBO.#T3') IS NOT NULL
DROP TABLE #T3
GO

CREATE TABLE #T3
(WAREHOUSE VARCHAR(10),
 ITEM VARCHAR(10),
 QTY INT)

INSERT INTO #T3
SELECT 'A','P001',10 UNION ALL
SELECT 'A','P002',20 UNION ALL
SELECT 'C','P001',15 UNION ALL
SELECT 'C','P003',10


SELECT COALESCE(A.WAREHOUSE,B.WAREHOUSE,C.WAREHOUSE) AS WAREHOUSE,
COALESCE(A.ITEM,B.ITEM,C.ITEM) AS ITEM,
ISNULL(A.QTY,0) AS QTY_BEGIN,ISNULL(B.QTY_DELIVERY,0),ISNULL(C.QTY_PURCHASE,0),
ISNULL(A.QTY,0)+ISNULL(C.QTY_PURCHASE,0)-ISNULL(B.QTY_DELIVERY,0) AS BALANCE
FROM #T1 A
FULL JOIN (SELECT WAREHOUSE,ITEM,SUM(QTY) AS QTY_DELIVERY FROM #T2 GROUP BY WAREHOUSE,ITEM) AS B ON A.ITEM=B.ITEM AND A.WAREHOUSE=B.WAREHOUSE
FULL JOIN (SELECT WAREHOUSE,ITEM,SUM(QTY) AS QTY_PURCHASE FROM #T3 GROUP BY WAREHOUSE,ITEM) AS C ON ISNULL(A.ITEM,B.ITEM)=C.ITEM AND ISNULL(A.WAREHOUSE,B.WAREHOUSE)=C.WAREHOUSE