create table T(type varchar(10),num int, num1 int)
insert into T
select 'in',5,null union all
select 'in',9,null union all
select 'out',3,null union all
select 'in',4,null
declare @num int,@sum int
select @num=0,@sum=0
update T
set num1=@num+@sum,
@sum=@num+@sum,
@num=case when type='in' then num else -num end
select * from T
/*
type num num1
---------- ----------- -----------
in 5 5
in 9 14
out 3 11
in 4 15
*/
drop table detail
go
create table detail(业务类型 varchar(10),数量 int)
insert into detail
select '入库',5
union all select '入库',9
union all select '销售',3
union all select '入库',4
alter table detail add id int identity(1,1)
select 业务类型,数量,(select sum(case when 业务类型='入库' then 数量 when 业务类型='销售' then -数量 else 0 end) from detail t where detail.id>=t.id) as '发生后库存余额'
from detail
create table T(id int identity(1,1),type varchar(10),num int, num1 int)
insert into T
select 'in',5,null union all
select 'in',9,null union all
select 'out',3,null union all
select 'in',4,null
select * from T
select type,num,
last_money=(select sum(case when type='in' then num else -num end) from T where id<=A.id)
from T A
--結果
type num last_money
in 5 5
in 9 14
out 3 11
in 4 15
----------------------------------------沟
declare @sum int
select @sum=0
update T
set num1=@sum,
@sum=(case when type='in' then num else -num end)+@sum