SQL Server 循环 - 如何遍历一组记录

SQL Server loop - how do I loop through a set of records

提问by Funky

how do I loop through a set of records from a select?

如何从选择中遍历一组记录？

So say for example I have a few records that I wish to loop through and do something with each record. Here's a primitive version of my select:

例如，假设我有一些记录，我希望循环遍历并对每条记录执行某些操作。这是我选择的原始版本：

select top 1000 * from dbo.table
where StatusID = 7 

Thanks

谢谢

回答by FloChanz

By using T-SQL and cursors like this :

通过像这样使用 T-SQL 和游标：

DECLARE @MyCursor CURSOR;
DECLARE @MyField YourFieldDataType;
BEGIN
    SET @MyCursor = CURSOR FOR
    select top 1000 YourField from dbo.table
        where StatusID = 7      

    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
      /*
         YOUR ALGORITHM GOES HERE   
      */
      FETCH NEXT FROM @MyCursor 
      INTO @MyField 
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

回答by sam yi

This is what I've been doing if you need to do something iterative... but it would be wise to look for set operations first.

如果你需要做一些迭代的事情，这就是我一直在做的事情……但首先寻找集合操作是明智的。

select top 1000 TableID
into #ControlTable 
from dbo.table
where StatusID = 7

declare @TableID int

while exists (select * from #ControlTable)
begin

    select top 1 @TableID = TableID
    from #ControlTable
    order by TableID asc

    -- Do something with your TableID

    delete #ControlTable
    where TableID = @TableID

end

drop table #ControlTable

回答by Precept

Small change to sam yi's answer(for better readability):

对sam yi 的回答的小改动（为了更好的可读性）：

select top 1000 TableID
into #ControlTable 
from dbo.table
where StatusID = 7

declare @TableID int

while exists (select * from #ControlTable)
begin

    select @TableID = (select top 1 TableID
                       from #ControlTable
                       order by TableID asc)

    -- Do something with your TableID

    delete #ControlTable
    where TableID = @TableID

end

drop table #ControlTable

回答by Agnel Amodia

By using cursor you can easily iterate through records individually and print records separately or as a single message including all the records.

通过使用游标，您可以轻松地单独遍历记录并单独打印记录或作为包含所有记录的单个消息。

DECLARE @CustomerID as INT;
declare @msg varchar(max)
DECLARE @BusinessCursor as CURSOR;

SET @BusinessCursor = CURSOR FOR
SELECT CustomerID FROM Customer WHERE CustomerID IN ('3908745','3911122','3911128','3911421')

OPEN @BusinessCursor;
    FETCH NEXT FROM @BusinessCursor INTO @CustomerID;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @msg = '{
              "CustomerID": "'+CONVERT(varchar(10), @CustomerID)+'",
              "Customer": {
                "LastName": "LastName-'+CONVERT(varchar(10), @CustomerID) +'",
                "FirstName": "FirstName-'+CONVERT(varchar(10), @CustomerID)+'",    
              }
            }|'
        print @msg
    FETCH NEXT FROM @BusinessCursor INTO @CustomerID;
END

回答by Sandeep

Just another approach if you are fine using temp tables.I have personally tested this and it will not cause any exception (even if temp table does not have any data.)

如果您可以使用临时表，这只是另一种方法。我已经亲自测试过，它不会导致任何异常（即使临时表没有任何数据。）

CREATE TABLE #TempTable
(
    ROWID int identity(1,1) primary key,
    HIERARCHY_ID_TO_UPDATE int,
)

--create some testing data
--INSERT INTO #TempTable VALUES(1)
--INSERT INTO #TempTable VALUES(2)
--INSERT INTO #TempTable VALUES(4)
--INSERT INTO #TempTable VALUES(6)
--INSERT INTO #TempTable VALUES(8)

DECLARE @MAXID INT, @Counter INT

SET @COUNTER = 1
SELECT @MAXID = COUNT(*) FROM #TempTable

WHILE (@COUNTER <= @MAXID)
BEGIN
    --DO THE PROCESSING HERE 
    SELECT @HIERARCHY_ID_TO_UPDATE = PT.HIERARCHY_ID_TO_UPDATE
    FROM #TempTable AS PT
    WHERE ROWID = @COUNTER

    SET @COUNTER = @COUNTER + 1
END


IF (OBJECT_ID('tempdb..#TempTable') IS NOT NULL)
BEGIN
    DROP TABLE #TempTable
END

回答by Bunkerbuster

You could choose to rank your data and add a ROW_NUMBER and count down to zero while iterate your dataset.

您可以选择对数据进行排名并添加 ROW_NUMBER 并在迭代数据集时倒计时至零。

-- Get your dataset and rank your dataset by adding a new row_number
SELECT  TOP 1000 A.*, ROW_NUMBER() OVER(ORDER BY A.ID DESC) AS ROW
INTO #TEMPTABLE 
FROM DBO.TABLE AS A
WHERE STATUSID = 7;

--Find the highest number to start with
DECLARE @COUNTER INT = (SELECT MAX(ROW) FROM #TEMPTABLE);
DECLARE @ROW INT;

-- Loop true your data until you hit 0
WHILE (@COUNTER != 0)
BEGIN

    SELECT @ROW = ROW
    FROM #TEMPTABLE
    WHERE ROW = @COUNTER
    ORDER BY ROW DESC

    --DO SOMTHING COOL  

    -- SET your counter to -1
    SET @COUNTER = @ROW -1
END

DROP TABLE #TEMPTABLE

回答by Monojit Sarkar

this way we can iterate into table data.

这样我们就可以迭代到表数据中。

DECLARE @_MinJobID INT
DECLARE @_MaxJobID INT
CREATE  TABLE #Temp (JobID INT)

INSERT INTO #Temp SELECT * FROM DBO.STRINGTOTABLE(@JobID,',')
SELECT @_MinJID = MIN(JobID),@_MaxJID = MAX(JobID)  FROM #Temp

    WHILE @_MinJID <= @_MaxJID
    BEGIN

        INSERT INTO Mytable        
        (        
            JobID,        
        )        

        VALUES        
        (        
            @_MinJobID,        
        ) 

        SET @_MinJID = @_MinJID + 1;
    END

DROP TABLE #Temp

STRINGTOTABLEis user define function which will parse comma separated data and return table. thanks

STRINGTOTABLE是用户定义的函数，它将解析逗号分隔的数据并返回表。谢谢

回答by u8917485

I think this is the easy way example to iterate item.

我认为这是迭代项目的简单方法示例。

declare @cateid int
select CateID into [#TempTable] from Category where GroupID = 'STOCKLIST'

while (select count(*) from #TempTable) > 0
begin
    select top 1 @cateid = CateID from #TempTable
    print(@cateid)

    --DO SOMETHING HERE

    delete #TempTable where CateID = @cateid
end

drop table #TempTable