SQL用游标怎么计算

发布网友发布时间：2022-04-07 20:07

共1个回答

热心网友时间：2022-04-07 21:36

use
tempdb
go
-->
-->
if
not
object_id('Tempdb..#T')
is
null
drop
table
#T
Go
Create
table
#T(ID
int
identity(1,1)
primary
key,[a1]
int,[a2]
int,[a3]
int)
Insert
#T
select
10,5,5
union
all
select
3,2,null
union
all
select
4,6,null
union
all
select
6,1,null
Go
declare
roy_cursor
cursor
LOCAL
for
select
[a1],[a2],[a3]
from
#T
declare
@snum
int,@a3
int,@a1
int,@a2
int
open
roy_cursor
fetch
next
from
roy_cursor
into
@a1,@a2,@a3
set
@snum=0
while
@@fetch_status=0
begin
update
#T
set
[a3]=@snum,@snum=@snum+isnull(@a1,0)-isnull(@a2,0)
where
current
of
roy_cursor
fetch
next
from
roy_cursor
into
@a1,@a2,@a3
end
close
roy_cursor
deallocate
roy_cursor
go
select
*
from
#T
--方法2:
go
update
t
set
[a3]=(select
sum([a1]-isnull([a2],0))
from
#T
where
ID<=t.ID)
from
#T
t
select
*
from
#T
写多一种方法3:
declare
@i
int
set
@i=0
update
#T
set
a3=@i,@i=@i+isnull(a1,0)-isnull(a2,0)
go
有大小关系时，没有可新一个用自增列，更新完再删除
ID
a1
a2
a3
-----------
-----------
-----------
-----------
1
10
5
5
2
3
2
6
3
4
6
4
4
6
1
9
(4
个资料列受到影响)
go