----第一步建表
create table #tbl(A varchar(max) ,B int ,C int,D int )
insert into #tbl values('1',9,4,102)
insert into #tbl values('1',1,2,0)
insert into #tbl values('1',1,5,0)
insert into #tbl values('1',2,5,0)
insert into #tbl values('1',3,5,0)
insert into #tbl values('1',4,5,0)
insert into #tbl values('1',4,7,0)
insert into #tbl values('1',4,8,0)
insert into #tbl values('1',5,8,0)
select * from #tbl
-------------------------------第二一下代码 想实现(表#tbl) D=D+B-C
----------执行下代码会提示 ：：
--消息 16931，级别 16，状态 1，第 15 行
--当前提取缓冲区内没有行。
declare @num int,@ii int
select @num=COUNT(*) from #tbl
set @ii=1
declare my_cursor cursor scroll dynamic for select B,C,D from #tbl
open my_cursor
declare @a int ,@b int ,@c int ,@aa int ,@bb int,@cc int,@dd int
fetch next from my_cursor into @a ,@b ,@c
while(@@fetch_status=0)
begin
if @ii=1
set @cc=@c
fetch next from my_cursor into @a ,@b ,@c
update #tbl set D=@cc+@a-@b where current of my_cursor
set @ii=@ii+1
set @cc=@cc+@a-@b
end
close my_cursor
deallocate my_cursor
select * from #tbl
------解决方案--------------------
IF OBJECT_ID('tbl_a') IS NOT NULL DROP table tbl_a
GO
create table tbl_a(A varchar(10) ,B int ,C int,D int )
insert into tbl_a values('期初',0,0,102)
insert into tbl_a values('1',1,2,0)
insert into tbl_a values('1',1,5,0)
insert into tbl_a values('1',2,5,0)
insert into tbl_a values('1',3,5,0)
insert into tbl_a values('1',4,5,0)
insert into tbl_a values('1',4,7,0)
insert into tbl_a values('1',4,8,0)
insert into tbl_a values('1',5,8,0)
select * from tbl_a
-------------------------------第二一下代码 想实现(表tbl_a) D=D+B-C
----------执行下代码会提示 ：：
--消息 16931，级别 16，状态 1，第 15 行
--当前提取缓冲区内没有行。
declare @num int,@ii int
select @num=COUNT(*) from tbl_a
set @ii=1
declare my_cursor cursor scroll dynamic for select B,C,D from tbl_a
open my_cursor
declare @a int ,@b int ,@c int ,@aa int ,@bb int,@cc int,@dd int
fetch next from my_cursor into @a ,@b ,@c
while(@@fetch_status=0)
begin
if @ii=1
set @cc=@c
fetch next from my_cursor into @a ,@b ,@c
if @@fetch_status=0
update tbl_a set D=@cc+@a-@b where current of my_cursor
set @ii=@ii+1
set @cc=@cc+@a-@b
end
close my_cursor
deallocate my_cursor
select * from tbl_a