SQL中的临时表或游标怎么循环获得其中的某一条记录
SQL中的临时表或游标如何循环获得其中的某一条记录?
Declare @Id as integer
Declare @Id1 as integer
declare cursor_table cursor for
select id from #Data_get1
open cursor_table
fetch next from cursor_table into @id
while(@@fetch_status=0)
begin
set @id1 = @id
update #Data_get2 set replyzsl = (select count(1) from test where id2=@id1) where id=@id1
end
Close cursor_table
DEALLOCATE cursor_table
我就是想循环 cursor_table 中的记录,一条一条地循环下来,可以得到每一条记录的id, 用以id 去更新另一个临时表的记录,请帮忙
以上好象不对!请高手指正,谢谢!
------解决方案--------------------
语法没问题
不知道你的逻辑是否正确
------解决方案--------------------
Declare @Id as integer
Declare @Id1 as integer
declare cursor_table cursor for
select id from #Data_get1
open cursor_table
fetch next from cursor_table into @id
while(@@fetch_status=0)
begin
set @id1 = @id
update #Data_get2 set replyzsl = (select count(1) from test where id2=@id1) where id=@id1
end
Close cursor_table
DEALLOCATE cursor_table
我就是想循环 cursor_table 中的记录,一条一条地循环下来,可以得到每一条记录的id, 用以id 去更新另一个临时表的记录,请帮忙
以上好象不对!请高手指正,谢谢!
------解决方案--------------------
语法没问题
不知道你的逻辑是否正确
------解决方案--------------------
- SQL code
while(@@fetch_status=0) begin set @id1 = @id update #Data_get2 set replyzsl = (select count(1) from test where id2=@id1) where id=@id1 --至少你要加入以下语句避免死循环 fetch next from cursor_table into @id end
------解决方案--------------------
应该就是少了fetch next from cursor_table into @id
语句导致你不能取到下一条记录。在第一条记录上死循环了
------解决方案--------------------
------解决方案--------------------
- SQL code
--楼主可以这样不用 游标,不会锁表,效率也很高 declare @Rows int, @Row int, @ID int set @Row = 1 declare @t table( Row int identity(1,1) not null, Id int not null ) insert into @t select id from #Data_get1 set @Rows = @@ROWCOUNT while(@Row <=@Rows) begin select @id = id from @t where Row = @Row update #Data_get2 set replyzsl = (select count(1) from test where id2=@id) where id=@id set @row = @row + 1 end