blocks|key|894405|text|T-SQL没有FOR循环，它有一个WHILE循环|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|894406|WHILE+(Transact-SQL)|894407|WHILE+Boolean_expression
BEGIN

END|code-block|syntax|javascript|894408|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql^0|7|3|H|5|0|0|K|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@]|D|@$9|10|A|11|1|12]]|E|$]]|$1|H|3|I|5|J|7|13|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

T-SQL doesn't have a <code>FOR</code> loop, it has a <code>WHILE</code> loop 
<a href="https://docs.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql" rel="noreferrer">WHILE (Transact-SQL)</a>

<pre><code>WHILE Boolean_expression
BEGIN

END
</code></pre>

blocks|key|677710|text|没有for-loop，只有while-loop：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|677711|DECLARE+@i+int+=+0

WHILE+@i+<+20
BEGIN
++++SET+@i+=+@i+%2B+1
++++/*+do+some+work+*/
END|code-block|syntax|javascript|677712|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

There is no for-loop, only the while-loop:

<pre class="lang-sql prettyprint-override"><code>DECLARE @i int = 0

WHILE @i &lt; 20
BEGIN
 SET @i = @i + 1
 /* do some work */
END
</code></pre>

blocks|key|894560|text|额外信息|type|unstyled|depth|inlineStyleRanges|entityRanges|data|894561|只是补充一下，因为没有人发布了一个答案，其中包括如何在循环中实际迭代数据集。您可以使用关键字OFFSET+FETCH。|offset|length|894562|用法|894563|DECLARE+@i+INT+=+0;
SELECT+@count=++Count(*)+FROM+{TABLE}

WHILE+@i+<=+@count
BEGIN
+++++++
++++SELECT+*+FROM+{TABLE}
++++ORDER+BY+{COLUMN}
++++OFFSET+@i+ROWS+++
++++FETCH+NEXT+1+ROWS+ONLY++

++++SET+@i+=+@i+%2B+1;

END|code-block|syntax|javascript|894564|entityMap|0|LINK|mutability|MUTABLE|url|https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx)^0|0|1A|C|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@$D|W|E|X|1|Y]]|A|$]]|$1|F|3|G|5|6|7|Z|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|10|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

<h2>Extra Info</h2>
Just to add as no-one has posted an answer that includes how to actually iterate over a dataset inside a loop. You can use the keywords <a href="https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx" rel="noreferrer">OFFSET FETCH</a>.
Usage
<pre><code>DECLARE @i INT = 0;
SELECT @count= Count(*) FROM {TABLE}

WHILE @i &lt;= @count
BEGIN
 
 SELECT * FROM {TABLE}
 ORDER BY {COLUMN}
 OFFSET @i ROWS 
 FETCH NEXT 1 ROWS ONLY 

 SET @i = @i + 1;

END
</code></pre>

blocks|key|677721|text|​|type|unstyled|depth|inlineStyleRanges|entityRanges|data|677722|DECLARE+@intFlag+INT
SET+@intFlag+=+1
WHILE+(@intFlag+<=5)+
BEGIN
++++PRINT+@intFlag
++++SET+@intFlag+=+@intFlag+%2B+1
END
GO|code-block|syntax|javascript|677723|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag &lt;=5) 
BEGIN
 PRINT @intFlag
 SET @intFlag = @intFlag + 1
END
GO
</code></pre>

blocks|key|677754|text|这样如何：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|677755|BEGIN
+++Do+Something
END
GO+10|code-block|syntax|javascript|677756|..。当然，如果需要计数，您可以在其中放置一个递增计数器。|677757|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

How about this:

<pre><code>BEGIN
 Do Something
END
GO 10
</code></pre>

... of course you could put an incremental counter inside it if you need to count.

blocks|key|894686|text|试一试，学习它：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|894687|DECLARE+@r+INT+=+5
DECLARE+@i+INT+=+0
DECLARE+@F+varchar(max)+=+''
WHILE+@i+<+@r
BEGIN

++++DECLARE+@j+INT+=+0
++++DECLARE+@o+varchar(max)+=+''
++++WHILE+@j+<+@r+-+@i+-+1
++++BEGIN
++++++++SET+@o+=+@o+%2B+'+'
++++++++SET+@j+%2B=+1
++++END

++++DECLARE+@k+INT+=+0
++++WHILE+@k+<+@i+%2B+1
++++BEGIN
++++++++SET+@o+=+@o+%2B+'+*'++--+'*'
++++++++SET+@k+%2B=+1
++++END
++++SET+@i+%2B=+1
++++SET+@F+=+@F+%2B+@o+%2B+CHAR(13)
END
PRINT+@F|code-block|syntax|javascript|894688|日期：|894689|DECLARE+@d+DATE+=+'2019-11-01'
WHILE+@d+<+GETDATE()
BEGIN
++++PRINT+@d
++++SET+@d+=+DATEADD(DAY,1,@d)
END
PRINT+'n'
PRINT+@d|894690|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Try it, learn it: 

<pre><code>DECLARE @r INT = 5
DECLARE @i INT = 0
DECLARE @F varchar(max) = ''
WHILE @i &lt; @r
BEGIN

 DECLARE @j INT = 0
 DECLARE @o varchar(max) = ''
 WHILE @j &lt; @r - @i - 1
 BEGIN
 SET @o = @o + ' '
 SET @j += 1
 END

 DECLARE @k INT = 0
 WHILE @k &lt; @i + 1
 BEGIN
 SET @o = @o + ' *' -- '*'
 SET @k += 1
 END
 SET @i += 1
 SET @F = @F + @o + CHAR(13)
END
PRINT @F
</code></pre>

With date:

<pre><code>DECLARE @d DATE = '2019-11-01'
WHILE @d &lt; GETDATE()
BEGIN
 PRINT @d
 SET @d = DATEADD(DAY,1,@d)
END
PRINT 'n'
PRINT @d
</code></pre>

blocks|key|894631|text|T-SQL中的While循环示例，其中列出了当月的开始到结束日期。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|894632|DECLARE+@Today+DATE=+GETDATE()+,
@StartOfMonth+DATE+,
@EndOfMonth+DATE;

DECLARE+@DateList+TABLE+(+DateLabel+VARCHAR(10)+);
SET+@EndOfMonth+=+EOMONTH(GETDATE());
SET+@StartOfMonth+=+DATEFROMPARTS(YEAR(@Today),+MONTH(@Today),+1);

WHILE+@StartOfMonth+<=+@EndOfMonth
BEGIN
++++INSERT++INTO+@DateList
++++VALUES++(+@StartOfMonth+);
++++SET+@StartOfMonth+=+DATEADD(DAY,+1,+@StartOfMonth);
END;

SELECT++DateLabel
FROM++++@DateList;++|code-block|syntax|javascript|894633|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

While Loop example in T-SQL which list current month's beginning to end date.

<pre><code>DECLARE @Today DATE= GETDATE() ,
@StartOfMonth DATE ,
@EndOfMonth DATE;

DECLARE @DateList TABLE ( DateLabel VARCHAR(10) );
SET @EndOfMonth = EOMONTH(GETDATE());
SET @StartOfMonth = DATEFROMPARTS(YEAR(@Today), MONTH(@Today), 1);

WHILE @StartOfMonth &lt;= @EndOfMonth
BEGIN
 INSERT INTO @DateList
 VALUES ( @StartOfMonth );
 SET @StartOfMonth = DATEADD(DAY, 1, @StartOfMonth);
END;

SELECT DateLabel
FROM @DateList; 
</code></pre>

blocks|key|894750|text|旧的线程，但仍在不断出现，我想我应该为那些需要的人提供一个"FOREACH“解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|894751|DECLARE+@myValue+nvarchar(45);
DECLARE+myCursor+CURSOR+FOR
SELECT+[x]+
FROM+(Values+('Value1'),('Value2'),('Value3'),('Value4'))+
as+MyTable(x);
OPEN+myCursor;
FETCH+NEXT+FROM+myCursor+INTO+@myValue;
While+(@@FETCH_STATUS+=+0)
BEGIN
++++PRINT+@myValue
++++FETCH+NEXT+FROM+myCursor+INTO+@myValue;
END
CLOSE+myCursor;+++++
DEALLOCATE+myCursor;|code-block|syntax|javascript|894752|我应该郑重声明，递归在SQL世界中是不受欢迎的。而且有很好的理由-它可能对性能非常不利。尽管如此，对于维护/离线/批量/ad-hoc/测试/等操作，我经常使用这种方法。|894753|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Old thread but still coming up and I thought I would offer a &quot;FOREACH&quot; solution for those that need one.
<pre><code>DECLARE @myValue nvarchar(45);
DECLARE myCursor CURSOR FOR
SELECT [x] 
FROM (Values ('Value1'),('Value2'),('Value3'),('Value4')) 
as MyTable(x);
OPEN myCursor;
FETCH NEXT FROM myCursor INTO @myValue;
While (@@FETCH_STATUS = 0)
BEGIN
 PRINT @myValue
 FETCH NEXT FROM myCursor INTO @myValue;
END
CLOSE myCursor; 
DEALLOCATE myCursor;
</code></pre>
I should state for the record that recursion is frowned upon in the SQL world. And for good reason - it can be very detrimental to performance. Still, for maintenance/offline/bulk/ad-hoc/testing/etc operations, I use this method a lot.

What is the syntax of a <code>for</code> loop in TSQL?

Syntax of for-loop in SQL Server

在TSQL中，for循环的语法是什么？

问SQL Server中for-loop的语法
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server中for-loop的语法EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server中for-loop的语法
EN