b:Select 商品名,Sum(数量) As 数量,Sum(金额) AS 金额 From b Group By b
a Select a.商品名,Sum(isnull(a.数量,0)- Isnull(c.数量,0) )From a Left Join c On a.商品名 = b.商品名 Group By a.商品名
a Select a.商品名,Sum(isnull(a.数量,0)- Isnull(c.数量,0) )From a Left Join c On a.商品名 = b.商品名 Group By a.商品名
以单价为标记马?select t1.商品名,t1.规格,t1.单价,t1.数量-isnull(t2.数量,0) as 数量,t1.单价*(t1.数量-isnull(t2.数量,0)) as 金额 from
(
select * from a
union all
select * from b
)t1 left join c t2 on t1.商品名=t2.商品名 and t1.单价=t2.单价
isnull(a.数量,0)+isnull(b.数量)-isnull(c.数量),
isnull(a.金额)+isnull(b.金额)-isnull(c.金额)
from a,b,c
where a.商品名 = b.商品名 and a.商品名 =c.商品名
create table a表(商品名 char(1), 规格 char(3),单价 money, 数量 int,金额 int)
insert into 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
create table b表(商品名 char(1),规格 char(3),单价 money,数量 int,金额 int)
insert into 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,10create table c表(商品名 char(1),单价 money,数量 int,金额 int)
insert into c表
select 'a',0.2,5,1 union
select 'c',0.1,10,1 union
select 'e',0.5,10,5select 商品名,sum(数量) 数量 from(
select 商品名,数量 from a表
union all
select 商品名,数量 from b表
union all
select 商品名,-(数量) from c表)m
group by 商品名drop table a表
drop table b表
drop table c表
isnull(a.数量,0)+isnull(b.数量,0)-isnull(c.数量,0),
isnull(a.金额,0)+isnull(b.金额,0)-isnull(c.金额,0)
from a,b,c
where a.商品名 = b.商品名 and a.商品名 =c.商品名
(select d.商品名 as 商品名,d.sum(数量) as 数量,d.sum(金额) as 金额 from
(select 商品名,规格,单价,数量,金额 from a
union select 商品名,规格,单价,数量,金额 from b)d --先把所有的入库纪录合并
group by d.商品名
)e --然后汇总到表e
left join c on e.商品名 = c.商品名 --最后库存汇总表和出库左连接这种方法必须保证c表的商品名不重复(a表,b表的可以重复,即多笔入库纪录)2.如果重复,则c表还要做这样的处理
select e.商品名 ,(e.数量 - isnull(c.数量,0)) as .数量,(e.金额 - isnull(c.金额,0)) as 金额
(select d.商品名 as 商品名,d.sum(数量) as 数量,d.sum(金额) as 金额 from
(select 商品名,规格,单价,数量,金额 from a
union select 商品名,规格,单价,数量,金额 from b)d --先把所有的入库纪录合并
group by d.商品名
)e --然后汇总到表e
left join
(select c.商品名 as 商品名,c.sum(数量) as 数量,c.sum(金额) as 金额 from c)f--汇总出库资料到f表
on e.商品名 = f.商品名 --最后库存汇总表和出库左连接
--测试数据
create table #a (nam varchar(10),num int,price decimal)
create table #b (nam varchar(10),num int,price decimal)
create table #c (nam varchar(10),num int,price decimal)insert into #a values('a',10,2)
insert into #a values('b',20,12)
insert into #a values('c',30,3)
insert into #a values('d',20,16)
insert into #a values('e',10,5)insert into #b values('a',30,6)
insert into #b values('d',60,6)
insert into #b values('e',20,10)insert into #c values('a',5,1)
insert into #c values('d',10,1)
insert into #c values('e',10,5)select * from #a
select * from #b
select * from #c
/*
nam num price
---------- ----------- --------------------
a 10 2
b 20 12
c 30 3
d 20 16
e 10 5(5 row(s) affected)nam num price
---------- ----------- --------------------
a 30 6
d 60 6
e 20 10(3 row(s) affected)nam num price
---------- ----------- --------------------
a 5 1
d 10 1
e 10 5(3 row(s) affected)
*/select nam = (
case when #a.nam is not null then #a.nam
when #b.nam is not null then #b.nam
else #c.nam
end),--用的是full join,如果a.nam不空则取a.nam,否则取b.nam(else不会取到,没有库存不能出库)
sum(isnull(#a.num,0) + isnull(#b.num,0) - isnull(#c.num,0)) as num,
sum(isnull(#a.price,0) + isnull(#b.price,0) - isnull(#c.price,0)) as price from
#a full join #b on #a.nam = #b.nam
full join #c on #a.nam =#c.nam
group by
case when #a.nam is not null then #a.nam
when #b.nam is not null then #b.nam
else #c.nam
end/*
nam num price
---------- ----------- ----------------------------------------
a 35 7
b 20 12
c 30 3
d 70 21
e 20 10(5 row(s) affected)*/
(select a.spm,
a.gg,
a.dj,
sl = a.sl - IsNull(c.sl,0),
je = a.je - IsNull(c.je,0)
from (select spm,gg,dj,sl = sum(sl),je = sum(je) from @a group by spm,gg,dj) a
left join
(select spm,dj,sl = IsNull(sum(IsNull(sl,0)),0),je = IsNull(sum(IsNull(je,0)),0) from @c group by spm,dj) c
on a.spm = c.spm and a.dj = c.dj
union
select spm,gg,dj,sl,je from @b) d
group by spm,gg,dj
declare @a table
( spm char(1),
gg char(3),
dj money,
sl int,
je int)
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
declare @b table
( spm char(1),
gg char(3),
dj money,
sl int,
je int)
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,10declare @c table
( spm char(1),
dj money,
sl int,
je int)
insert @c
select 'a',0.2,5,1 union
select 'c',0.1,10,1 union
select 'e',0.5,10,5
--结果
spm gg dj sl je
a 1*1 0.20 35 7
b 3*5 0.60 20 12
c 2*4 0.10 80 8
d 1*2 0.80 20 16
e 2*2 0.50 20 10