--期初数据
DECLARE @stock TABLE(id int,num decimal(10,2))
INSERT @stock SELECT 1,100
UNION ALL SELECT 3,500
UNION ALL SELECT 4,800
--入库数据
DECLARE @in TABLE(id int,num decimal(10,2))
INSERT @in SELECT 1,100
UNION ALL SELECT 1,80
UNION ALL SELECT 2,800
--出库数据
DECLARE @out TABLE(id int,num decimal(10,2))
INSERT @out SELECT 2,100
UNION ALL SELECT 3,100
UNION ALL SELECT 3,200
--统计
SELECT id,
stock_opening=SUM(stock_opening),
stock_in=SUM(stock_in),
stock_out=SUM(stock_out),
stock_closing=SUM(stock_closing)
FROM(
SELECT id,stock_opening=num,stock_in=0,stock_out=0,stock_closing=num
FROM @stock
UNION ALL
SELECT id,stock_opening=0,stock_in=num,stock_out=0,stock_closing=num
FROM @in
UNION ALL
SELECT id,stock_opening=0,stock_in=0,stock_out=num,stock_closing=-num
FROM @out
)a GROUP BY id
/*--结果
id stock_opening stock_in stock_out stock_closing
---------------- ----------------------- ----------------- -------------------- --------------------
1 100.00 180.00 .00 280.00
2 .00 800.00 100.00 700.00
3 500.00 .00 300.00 200.00
4 800.00 .00 .00 800.00
--*/
