使用UNION做进销存报表

--期初数据
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
--*/