如何在sql中循环检索列值？ [英] How to retrieve column values looping wise in sql?

问题描述

通过从实际表中过滤数据我将其插入临时表然后一些批量值来自它然后我再次将其插入到另一个临时表中以进行检查循环并将实际表中的一些值更新到数据库中，所以如何检索循环明确的列值？直到这里我到达了

By filtering data from actual table i am inserting it into temporary table then some bulk values are coming from it then again i am inserting it into another temporary table for check loop wise and update some values in actual table into database, so how to retrieve column values looping wise? till here i reached

CREATE TABLE #result 
        (           
           PackagePeriod     varchar(20),
           UserId            INT,           
           OldExpiryDate     DATE,         
           AmountToPay       FLOAT,
           PyingAmount       FLOAT,
           Balance           FLOAT,
           LastPaidDate      DATE,
           Company_Id        INT           
        )

create table #temp
        (
            PackagePeriod    varchar(20),
           UserId            INT,           
           OldExpiryDate     DATE,         
           AmountToPay       FLOAT,
           PyingAmount       FLOAT,
           Balance           FLOAT,
           LastPaidDate      DATE,
           Company_Id        INT
        )           

        SET @cmd ='.... where OldExpiryDate<=Replace(CONVERT(VARCHAR(20), (GetDate()-1), 106),' ','-')

    select @count=(select COUNT(PackagePeriod) from #temp)

    if(PackagePeriod='Monthly')
    begin
    update ...
    end
    else if(PackagePeriod='Quarterly')
    begin
    update ..

推荐答案

---从我上面的问题中我能理解的内容





DECLARE @count INT

DECLARE @I INT

DECLARE @PackageVar NVARCHAR（100）

DECLARE @Loop INT



SET @Loop = 100



SELECT @I = 1

SELECT @count = COUNT（PackagePeriod）FROM＃ temp



WHILE（@I< = @count）

BEGIN



SELECT @PackageVar = PackagePeriod

FROM #temp WHERE Company_Id = @Loop - 从100获取主要ID（Company_Id）



如果（@PackageVar =='每月'）

UPDATE

Else IF（@Pack ageVar =='季刊'）

更新

SELECT @I = @I + 1

SELECT @Loop = @Loop + 1

END

--- From whatever i can understand from your question above


DECLARE @count INT
DECLARE @I INT
DECLARE @PackageVar NVARCHAR(100)
DECLARE @Loop INT

SET @Loop = 100

SELECT @I = 1
SELECT @count = COUNT(PackagePeriod) FROM #temp

WHILE (@I <= @count )
BEGIN

SELECT @PackageVar = PackagePeriod
FROM #temp WHERE Company_Id = @Loop --Assuming Primary ID(Company_Id ) from 100

If(@PackageVar == 'Monthly')
UPDATE
Else IF(@PackageVar == 'Quarterly')
UPDATE
SELECT @I = @I + 1
SELECT @Loop = @Loop + 1
END

CREATE TABLE #result 
        (           
           PackagePeriod     varchar(20),
           UserId            INT,           
           OldExpiryDate     DATE,         
           AmountToPay       FLOAT,
           PyingAmount       FLOAT,
           Balance           FLOAT,
           LastPaidDate      DATE,
           Company_Id        INT           
        )

declare @PackagePeriod     varchar(20)
declare @UserId            int          
declare @OldExpiryDate     DATE         
declare @AmountToPay       FLOAT
declare @PyingAmount       FLOAT
declare @Balance           FLOAT
declare @LastPaidDate      DATE
declare @Company_Id        INT           

declare result_cursor cursor for 
        SELECT PackagePeriod, UserId,  OldExpiryDate,  AmountToPay, PyingAmount, Balance, LastPaidDate, Company_Id from  #result

OPEN result_cursor

FETCH NEXT FROM result_cursor INTO @PackagePeriod, @UserId,  @OldExpiryDate,  @AmountToPay, @PyingAmount, @Balance, @LastPaidDate, @Company_Id

WHILE @@FETCH_STATUS = 0

BEGIN

    if(@PackagePeriod='Monthly')
    begin
       -- update your table 
    end
    else if(@PackagePeriod='Quarterly')
    begin
        --update table
    end

  FETCH NEXT FROM result_cursor INTO @PackagePeriod, @UserId,  @OldExpiryDate,  @AmountToPay, @PyingAmount, @Balance, @LastPaidDate, @Company_Id

END

CLOSE result_cursor

DEALLOCATE result_cursor

这篇关于如何在sql中循环检索列值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！