在不明确用一条记录中收入,发出两个数据组合的规则之前,给你一个思路:---------------------------------------------------------------------------
--商品结存表
create table tblA(
fcode varchar(10),
famount int
)insert into tblA select '001.01',30
--商品收发表
create table tblB(
fcode varchar(10),
fqty int,
ftype int
) insert into tabB select '001.01',2 ,0
insert into tabB select '001.01',5 ,1
insert into tabB select '001.01',3 ,0
insert into tabB select '001.01',20,0
insert into tabB select '001.01',30,1
--生成临时表
select
identity(int,1,1) as ID,
c.*
into
#t
from
(select
fcode as 商品编码,
famount as 期初 ,
null as 收入 ,
null as 发出
from
tblA
union
select
a.fcode as 商品编码,
a.famount as 期初 ,
case ftype when 0 then fqty else 0 end as 收入 ,
case ftype when 1 then fqty else 0 end as 发出
from
tblA a
inner join
tblB b
on
a.fcode = b.fcode) c--查询
select
c.商品编码,
c.期初 ,
c.收入 ,
c.发出 ,
结存 = (c.期初 - d.total)
from
#t c,
(select
b.ID,
sum(isnull(a.发出,0) - isnull(a.收入,0)) as total
from
#t a ,#t b
where
a.商品编码 = b.商品编码 and a.ID <= b.ID
group by
b.ID) d
where
c.ID = d.ID
order by
c.商品编码,c.ID
--输出:
商品编码 期初 收入 发出 结存
---------------------------------------
001.01 30 NULL NULL 30
001.01 30 0 5 25
001.01 30 0 30 -5
001.01 30 2 0 -3
001.01 30 3 0 0
001.01 30 20 0 20
--商品结存表
create table tblA(
fcode varchar(10),
famount int
)insert into tblA select '001.01',30
--商品收发表
create table tblB(
fcode varchar(10),
fqty int,
ftype int
) insert into tabB select '001.01',2 ,0
insert into tabB select '001.01',5 ,1
insert into tabB select '001.01',3 ,0
insert into tabB select '001.01',20,0
insert into tabB select '001.01',30,1
--生成临时表
select
identity(int,1,1) as ID,
c.*
into
#t
from
(select
fcode as 商品编码,
famount as 期初 ,
null as 收入 ,
null as 发出
from
tblA
union
select
a.fcode as 商品编码,
a.famount as 期初 ,
case ftype when 0 then fqty else 0 end as 收入 ,
case ftype when 1 then fqty else 0 end as 发出
from
tblA a
inner join
tblB b
on
a.fcode = b.fcode) c--查询
select
c.商品编码,
c.期初 ,
c.收入 ,
c.发出 ,
结存 = (c.期初 - d.total)
from
#t c,
(select
b.ID,
sum(isnull(a.发出,0) - isnull(a.收入,0)) as total
from
#t a ,#t b
where
a.商品编码 = b.商品编码 and a.ID <= b.ID
group by
b.ID) d
where
c.ID = d.ID
order by
c.商品编码,c.ID
--输出:
商品编码 期初 收入 发出 结存
---------------------------------------
001.01 30 NULL NULL 30
001.01 30 0 5 25
001.01 30 0 30 -5
001.01 30 2 0 -3
001.01 30 3 0 0
001.01 30 20 0 20
create table tblA(fcode varchar(10),famount int)
insert into tblA select '001.01',30--商品收发表
create table tblB(fcode varchar(10),fqty int,ftype int)
insert into tblB select '001.01',2 ,0
insert into tblB select '001.01',5 ,1
insert into tblB select '001.01',3 ,0
insert into tblB select '001.01',20,0
insert into tblB select '001.01',30,1
insert into tblB select '001.02',20,0
insert into tblB select '001.02',30,1
go--处理
--生成处理临时表
select fcode,fqty_in,fqty_out,fqty
into #t from(
select fcode
,fqty_in=case ftype when 0 then fqty else 0 end
,fqty_out=case ftype when 1 then fqty else 0 end
,fqty=0
,bz=1
from tblB
union all
select fcode,0,0,sum(famount),0
from( --期初数
select fcode,famount from tblA
union all
select fcode,0 from tblB --如果 tblA 中没有对应的期初数,则期初为0
)a group by fcode
)a order by fcode,bz--生成结存
declare @fcode varchar(10),@fqty int
update #t set
@fqty=case
when @fcode=fcode
then @fqty+isnull(fqty_in,0)-isnull(fqty_out,0)
else isnull(fqty,0)
end,@fcode=fcode,fqty=@fqty
select * from #t
drop table #t
go--删除测试
drop table tblA,tblB/*--测试结果fcode fqty_in fqty_out fqty
---------- ----------- ----------- -----------
001.01 0 0 30
001.01 2 0 32
001.01 0 5 27
001.01 3 0 30
001.01 20 0 50
001.01 0 30 20
001.02 0 0 0 --tblA中无期妆数据,则期初数为0
001.02 20 0 20
001.02 0 30 -10(所影响的行数为 9 行)
--*/
insert into tblB select 1,'001.01',2 ,0
insert into tblB select 2,'001.01',5 ,1
insert into tblB select 3,'001.01',3 ,0
insert into tblB select 4,'001.01',20,0
insert into tblB select 5,'001.01',30,1
insert into tblB select 6,'001.02',20,0
insert into tblB select 7,'001.02',30,1
select b.fcode, b.fqty, b.ftype ,
isnull((select sum(case ftype when 0 then fqty else -fqty end) from tblb where key1<b.key1),0)
+isnull(a.famount,0)
from tblB b left join tblA a on b.fcode=a.fcode order by b.key1
fcode fqty ftype
---------- ----------- ----------- -----------
001.01 2 0 30
001.01 5 1 32
001.01 3 0 27
001.01 20 0 30
001.01 30 1 50只是不包括初期数据
001.02 20 0 -10
001.02 30 1 10(所影响的行数为 7 行)
insert into tblB select 1,'001.01',2 ,0
insert into tblB select 2,'001.01',5 ,1
insert into tblB select 3,'001.01',3 ,0
insert into tblB select 4,'001.01',20,0
insert into tblB select 5,'001.01',30,1
insert into tblB select 6,'001.02',20,0
insert into tblB select 7,'001.02',30,1
select b.fcode, b.fqty, b.ftype ,
isnull((select sum(case ftype when 0 then fqty else -fqty end) from tblb where key1<b.key1),0)
+isnull(a.famount,0)
from tblB b left join tblA a on b.fcode=a.fcode order by b.key1
fcode fqty ftype
---------- ----------- ----------- -----------
001.01 2 0 30
001.01 5 1 32
001.01 3 0 27
001.01 20 0 30
001.01 30 1 50001.02 20 0 -10
001.02 30 1 10(所影响的行数为 7 行)只是不包括初期数据 如果你要,我可以帮你加进去,看你的需要啦
create table tblA(fcode varchar(10),famount int)
insert into tblA select '001.01',30--商品收发表
create table tblB(key1 int , fcode varchar(10),fqty int,ftype int)
insert into tblB select 1,'001.01',2 ,0
insert into tblB select 2,'001.01',5 ,1
insert into tblB select 3,'001.01',3 ,0
insert into tblB select 4,'001.01',20,0
insert into tblB select 5,'001.01',30,1
insert into tblB select 6,'001.02',20,0
insert into tblB select 7,'001.02',30,1
select b.fcode, case b.ftype when 0 then b.fqty else 0 end '收入',
case b.ftype when 0 then 0 else b.fqty end '支出',
isnull((select sum(case ftype when 0 then fqty else -fqty end) from tblb where key1<b.key1),0)
+isnull(a.famount,0)
from tblB b left join tblA a on b.fcode=a.fcode order by b.key1格式有点不对,现在改正。
只是不包括初期数据 如果你要,我可以帮你加进去,看你的需要啦 fcode 收入 支出
---------- ----------- ----------- -----------
001.01 2 0 30
001.01 0 5 32
001.01 3 0 27
001.01 20 0 30
001.01 0 30 50
001.02 20 0 -10
001.02 0 30 10(所影响的行数为 7 行)