这样的SQL能否实现?(下一条记录的值等于该行某字段的值加上上一条记录的值)

[xuehf]

描述如下:
商品001.01 期初数为 30

商品结存表 tblA
fcode famount
001.01 30

商品收发表 tblB ftype=0 收入 ftype=2 发出
fcode fqty ftype
001.01 2 0
001.01 5 1
001.01 3 0
001.01 20 0
001.01 30 1
......

商品编码 期初 收入 发出 结存
001.01 30 30
001.01 30 2 5 27
001.01 30 3 1 29
001.01 30 20 30 19
001.01 30 25 23 21

公式: 结存数=上一行的结存数+收入-支出(如果为第一行,则直接取自结存数)

[lishengyu]

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, 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 行）

[lishengyu]

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, 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 行）

只是不包括初期数据 如果你要，我可以帮你加进去，看你的需要啦

[lishengyu]

--商品结存表
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 行）

[子陌红尘]

在不明确用一条记录中收入，发出两个数据组合的规则之前，给你一个思路：

---------------------------------------------------------------------------
--商品结存表
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

[daijingjie2002]

你给出的计算条件太少，如根据出入库的id、日期等

[zjcxc]

--商品结存表
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 行）
--*/

[loveyzy]

邹老大为神人也!