如何遍历select中的一组记录?
例如,假设我有几条记录,我希望遍历这些记录,并对每条记录执行一些操作。下面是我的select的原始版本:
select top 1000 * from dbo.table
where StatusID = 7
谢谢
发布于 2013-12-18 23:47:13
通过像这样使用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;
发布于 2013-12-18 23:58:47
这就是我一直在做的,如果你需要做一些迭代的事情...但是,明智的做法是首先查找集合操作。此外,不要这样做,因为您不想学习游标。
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
发布于 2014-06-16 07:01:54
对sam yi's answer的小更改(为了更好的可读性):
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
https://stackoverflow.com/questions/20662356
复制相似问题