如何在 SQL Server 中循环遍历一组记录？

Question

如何循环遍历从select语句返回的一组记录？

比方说我有几条记录希望逐一处理。下面是一个简单版本的select语句：

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

- Funky

---

你想对每一个记录做什么？最好使用SQL查询完成任务。如果不行，你可能需要使用T-SQL，也许需要用到游标。 - Gordon Linoff

我会使用游标。 - FloChanz

那会很慢 - 重新编写存储过程或将其中一些逻辑移出以集合方式运行，这样不可行吗？ - Bridge

@Funky，这个存储过程是做什么的？通常代码可以以集合为基础进行重写（即避免循环）。如果您坚持要执行RBAR操作（https://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/），那么游标就是您想要研究的东西。 - gvee

也许您可以更详细地解释一下您将如何处理这些数据。在大多数情况下，您可以轻松编写一个单一的SQL查询，以执行您需要完成的操作，而无需循环遍历每个记录。 - Alan Barber

Answer 1

通过使用 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;

- FloChanz

---

正确的做法是重写该过程，使其不需要循环。在数据库中循环是一个极不明智的选择。 - HLGEM

也许你是对的，但根据我写答案时提供的信息，用户只是想循环遍历一组数据......而游标是一种实现它的方式。请注意，本翻译不包含解释或其他内容。 - FloChanz

游标只是一种工具，通常并没有对错之分。观察其性能并做出决策。这种回答（游标）是一种可能的选择。您也可以使用WHILE循环、公共表达式递归等等。 - Chains

@FrenkyB 是的，你可以。看这里... https://dev59.com/sWgu5IYBdhLWcg3w2adm - sam yi

恭喜！您的解决方案甚至在 MSDN 上都有：https://msdn.microsoft.com/en-us/library/ms180152.aspx#B-Using-FETCH-to-store-values-in-variables ，我真的很喜欢您如何使用字段数据类型。 - Pete

Answer 2

如果您需要进行重复操作，这就是我一直在做的事情……但是最好先查找集合操作。另外，请不要这样做，因为您不想学习光标。

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

- sam yi

---

谢谢！我使用上述代码进行更新和分组逻辑的示例：http://pastebin.com/GAjUNNi9。也许对任何人都有用。 - Nigrimmist

变量能否在循环内用作更新语句中的列名？类似于“Update TableName SET @ColumnName=2”。 - M H

这实际上回答了原始问题，因为它允许您迭代整个行；而游标让您迭代行中的特定列。 - I_do_python

“Cursors（游标）”比“while（循环）”语句冗长，但它们更优化。特别是在循环条件中有子查询的情况下，我不建议使用“while”语句。 - Christiano Kiss

@Christiano Kiss 我不太确定这个，因为相比于WHILE循环，CURSORS通常需要更长的时间来完成相同的任务。 - undefined

Answer 3

对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

- Precept

Answer 4

使用游标，您可以轻松地逐个迭代记录并单独打印记录或作为包括所有记录的单个消息一起打印。

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

- Agnel Amodia

---

这看起来很有趣。我想知道 @ 标识符的含义是什么。 - netskink

@只是用来区分变量的符号。 - Agnel Amodia

这绝对很有趣。但请启发我一下： ---> 'SET @msg ...' 部分如何从客户端获取数据？字段如何被识别为 LastName 或 FirstName 并返回值？从哪个变量中获取？如果您能解释一下，我会很感激。 - Tsiry Rakotonirina

Answer 5

您可以选择对数据进行排名并添加一个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

- Bunkerbuster

---

“ORDER BY” 在循环内是无用的。 - robotik

Answer 6

如果您可以使用临时表，这是另一种方法。我个人测试过，即使临时表没有任何数据，也不会引发任何异常。

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

- Sandeep

---

这真的很奇怪。它包含了很多错误，同时使用两个变量，其中一个从1到COUNT(*)，第二个从COUNT(*)到1也很奇怪。 - David Ferenczy Rogožan

变量MAXID用于循环。变量COUNTER用于对表中的特定记录执行操作。如果我读了问题，它谈到“有几条记录，我希望循环并对每个记录执行某些操作”。我可能错了，请指出上面的错误@DAWID。 - Sandeep

我认为在代码中使用这些变量是很明显的。你可以简单地写 WHILE (@COUTNER <= @ROWID)，而无需在每次迭代中递减 @ROWID。顺便问一下，如果你的表中的 ROWID 不连续（之前删除了某些行），会发生什么？ - David Ferenczy Rogožan

你会在什么情况下建议使用临时表而不是游标？这仅仅是一个设计选择，还是有更好的性能？ - h0r53

Answer 7

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

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

STRINGTOTABLE 是一个用户自定义函数，用于解析逗号分隔的数据并返回表格。谢谢。

- Monojit Sarkar

Answer 8

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

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

- 江明哲