首页新闻找找看学习计划

我的两重游标查询为什么查4分钟还在查询中?

0
悬赏园豆:20 [已解决问题] 解决于 2011-10-15 11:49

说明:

  1,入库表:Seekin_next

  2,出库表:Seekout_next

目的:显示各个仓库(storeid)每种产品(prdtid)的需求数量(pcount)和实际数量(amount)

语句如下:

SELECT top 10 [storeid]
      ,[pcount]
      ,[amount]
      ,[prdtid] into Seekin_next
  FROM Seekin

go

SELECT top 10 [storeid]
      ,[pcount]
      ,[amount]
      ,[prdtid] into Seekout_next
  FROM Seekout

go
If   Object_Id( 'Tempdb.dbo.#Sresult')   Is   Not   NULL--#Test 为临时表名
  begin
  Print 'Exists Table'
  drop table #Sresult
  end
Else begin
  Print 'Not Exists Table'
create table #Sresult
(
 [prdtid] [char](7) NOT NULL,
 [storeid] [nvarchar](20) NOT NULL,
 [pcount] [float] NULL,
 [amount] [money] NULL,
)
end


declare @prdtid1 char(7),@storeid1 nvarchar(20),@pcount1 float,@amount1 money
declare @prdtid2 char(7),@storeid2 nvarchar(20),@pcount2 float,@amount2 money
declare @pcount_result float,@amount_result money

declare cursor1 cursor for
 select * from Seekin_next
for read only
open cursor1
fetch next from cursor1
into @prdtid1,@storeid1,@pcount1,@amount1
while @@FETCH_STATUS=0
begin
 --内层循环开始
 set @pcount_result=0
 set @amount_result=0
 declare cursor2 cursor for
  select * from Seekout_next
 for read only
 open cursor2
 fetch next from cursor2
 into @prdtid2,@storeid2,@pcount2,@amount2
 while @@FETCH_STATUS=0
 begin
  if @prdtid1=@prdtid2 and @storeid1=@storeid2
  begin
   set @pcount_result=(@pcount1-@pcount2)+@pcount_result
   set @amount_result=(@amount1-@amount2)+@pcount_result
  end
 end
 insert into #Sresult (prdtid,storeid,pcount,amount) values (@prdtid1,@storeid1,@pcount_result,@amount_result)
 close cursor2
 deallocate cursor2
 --内层循环结束 
end
close cursor1
deallocate cursor1
select * from #Sresult
go

结果:等了4分钟还需要等待查询,为什么啊?救命啊!

szjdw的主页 szjdw | 初学一级 | 园豆:6
提问于:2011-10-15 11:34
< >
分享
最佳答案
1

两个循环内都没有是游标向下移动

在if后加上fetch next from cursor1
into @prdtid1,@storeid1,@pcount1,@amount1就可以了,呵呵!

收获园豆:20
xgcdd | 菜鸟二级 |园豆:225 | 2011-10-15 11:47
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册