我如何循环通过一组记录在SQL Server?

如何从选择语句循环遍历一组记录?

假设我有一些记录，我希望对它们进行循环，并对每个记录执行一些操作。下面是我的select语句的原始版本:

select top 1000 * from dbo.table
where StatusID = 7
670858 次浏览
小开

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

这就是我一直在做的，如果你需要做一些迭代…但首先寻找集合操作是明智的。另外，不要这样做，因为您不想学习游标。

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的回答的小改动(为了更好的可读性):

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
小开

如果您喜欢使用临时表，这是另一种方法。我亲自测试了这个，它不会导致任何异常(即使临时表没有任何数据)。

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
小开

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

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是用户定义函数，它将解析逗号分隔的数据并返回表。谢谢

小开

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

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

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
小开

我认为这是迭代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

提交答案