6.1.3 使用union实现库存报表的示例.sql

sqlserver 数据库编程的绝好脚本

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