用union all进行统计(好方法)

--当前库存
declare @cur table(id int,num int)
insert into @cur
select 1,100 union all select 2,200 union all select 3,300
--入库数
declare @in table(id int,num int)
insert into @in
select 1,300 union all select 3,400 union all select 4,700

--出库数
declare @out table(id int,num int)
insert into @out
select 1,200 union all select 2,500 union select 3,100

select * from
(
select id,curnum=num,innum = 0,outnum = 0 from @cur
union all
select id,curnum=0,innum = num,outnum=0 from @in
union all
select id,curnum = 0,innum = 0,outnum = num from @out ) a

---------------------
select id,innum = sum(innum),outnum = sum(outnum),curnum=sum(curnum)
from (
select id,curnum=num,innum = 0,outnum = 0 from @cur
union all
select id,curnum=0,innum = num,outnum=0 from @in
union all
select id,curnum = 0,innum = 0,outnum = num from @out ) a  group by id