查询在while循环中无法正常工作 [英] Query not working fine in while loop

问题描述

我有一个 While 循环，我想在其中插入.

I have a While loop where I am trying to insert.

              DECLARE @CurrentOffer int  =121
        DECLARE @OldestOffer int  = 115
        DECLARE @MinClubcardID bigint=0
              DECLARE @MaxClubcardID bigint=1000
                    WHILE 1 = 1
                        BEGIN
                        INSERT INTO Temp WITH (TABLOCK)
                        SELECT  top (100) clubcard  from TempClub   with (nolock) where ID between 
                        @MinClubcardand and @MaxClubcard

                        declare @sql varchar (8000)
                        while @OldestOffer <= @CurrentOffer
                        begin
                        print @CurrentOffer
                        print @OldestOffer

                                set @sql = 'delete from Temp where Clubcard 
                                 in (select Clubcard from ClubTransaction_'+convert(varchar,@CurrentOffer)+' with (nolock))'
                                 print (@sql)
                                 exec (@sql)

                                SET @CurrentOffer = @CurrentOffer-1  
                                IF @OldestOffer = @CurrentOffer
                                    begin

                                        -- my logic
                                    end

                        end
                    end

我的 TempClub 表总是只检查前 100 条记录.我的 TempClub 表有 3000 条记录.我需要使用 ClubTransaction_121,ClubTransaction_120,ClubTransaction_119 表检查我所有的俱乐部卡所有 3000 条记录.

My TempClub table always checks only with first 100 records. My TempClub table has 3000 records. I need to check all my clubcard all 3000 records with ClubTransaction_121,ClubTransaction_120,ClubTransaction_119 table.

推荐答案

为了进行批处理类型的处理，需要将@MinClubcardID 设置为最后处理的 ID 加 1 并包含一个 ORDER BY ID 以确保记录正在按顺序退回.

In order to do batch type processing, you need to set the @MinClubcardID to the last ID processed plus 1 and include an ORDER BY ID to ensure that the records are being returned in order.

但是...我不会使用使用主键作为我的索引"的方法.您正在寻找的是基本的分页模式.在 SQL Server 2005+ 中，Microsoft 引入了 row_number() 函数，它使分页变得更加容易.

But... I wouldn't use the approach of using the primary key as my "index". What you're looking for is a basic pagination pattern. In SQL Server 2005+, Microsoft introduced the row_number() function which makes pagination a lot easier.

例如:

 DECLARE @T TABLE (clubcard INT)

 DECLARE @start INT
 SET @start = 0

 WHILE(1=1)
 BEGIN
   INSERT @T (clubcard)
   SELECT TOP 100 clubcard FROM 
   (
      SELECT clubcard,
      ROW_NUMBER() OVER (ORDER BY ID) AS num
      FROM dbo.TempClub
   ) AS t
   WHERE num > @start

  IF(@@ROWCOUNT = 0) BREAK;

  -- update counter
  SET @start = @start + 100

  -- process records found

  -- make sure temp table is empty
  DELETE FROM @T
END

这篇关于查询在while循环中无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！