急求sql关于数据合

Question

select a.商品名,a.规格,a.单价,a.数量+b.数量-c.数量 as 库存,a.金额+b.金额-c.金额
from a,b,c
where a.商品名=b.商品名 and a.商品名=c.商品名

Answer 1

结果
a 1*1 0.20000000000000001 35.0 7.0
c 2*4 0.10000000000000001 80.0 8.0
e 2*2 0.5 20.0 10.0
a 1*1 0.20000000000000001 35.0 7.0
c 2*4 0.10000000000000001 80.0 8.0
e 2*2 0.5 20.0 10.0
a 1*1 0.20000000000000001 35.0 7.0
c 2*4 0.10000000000000001 80.0 8.0
e 2*2 0.5 20.0 10.0

Answer 2

declare @a table([商品名] varchar(10),[规格] varchar(10),[单价] decimal(12,1),[数量] int,[金额] money)
declare @b table([商品名] varchar(10),[规格] varchar(10),[单价] decimal(12,1),[数量] int,[金额] money)
declare @c table([商品名] varchar(10),[单价] decimal(12,1),[数量] int,[金额] money)insert @a
select 'a','1*1',0.2,10,2 union
select 'b','3*5',0.6,20,12 union
select 'c','2*4',0.1,30,3 union
select 'd','1*2',0.8,20,16 union
select 'e','2*2',0.5,10,5 insert @b
select 'a','1*1',0.2,30,6 union
select 'c','2*4',0.1,60,6 union
select 'e','2*2',0.5,20,10 insert @c
select 'a',0.2,5,1 union
select 'c',0.1,10,1 union
select 'e',0.5,10,5select A.[商品名],
       [数量]=A.[数量]-isnull(C.[数量],0),
       [金额]=A.[金额]-isnull(C.[金额],0)from (
       select [商品名],[数量]=sum([数量]),[金额]=sum(金额)
       from
       (
         select * from @a
         union all
         select * from @b
       )T group by [商品名]
     ) A
left join 
     (
        select [商品名],[数量]=sum([数量]),[金额]=sum(金额) from @c group by [商品名]
     ) C on A.[商品名]=C.[商品名]--结果
/*
商品名        数量          金额                    
---------- ----------- --------------------- 
a          35          7.0000
b          20          12.0000
c          80          8.0000
d          20          16.0000
e          20          10.0000（所影响的行数为 5 行）
*/