SQL Server FOR EACH 循环 [英] SQL Server FOR EACH Loop

问题描述

我有以下 SQL 查询:

I have the following SQL query:

DECLARE @MyVar datetime = '1/1/2010'    
SELECT @MyVar

这自然会返回1/1/2010".

This naturally returns '1/1/2010'.

我想做的是有一个日期列表，比如:

What I want to do is have a list of dates, say:

1/1/2010
2/1/2010
3/1/2010
4/1/2010
5/1/2010

然后我想对数字进行 FOR EACH 并运行 SQL 查询.

Then i want to FOR EACH through the numbers and run the SQL Query.

类似(伪代码):

List = 1/1/2010,2/1/2010,3/1/2010,4/1/2010,5/1/2010

For each x in List
do
  DECLARE @MyVar datetime = x

  SELECT @MyVar

所以这将返回:-

1/1/20102/1/20103/1/20102010 年 4 月 1 日2010/5/1

1/1/2010 2/1/2010 3/1/2010 4/1/2010 5/1/2010

我希望这将数据作为一个结果集返回，而不是多个结果集，因此我可能需要在查询结束时使用某种联合，因此循环的每次迭代都将联合到下一个.

I want this to return the data as one resultset, not multiple resultsets, so I may need to use some kind of union at the end of the query, so each iteration of the loop unions onto the next.

编辑

我有一个接受截止日期"参数的大型查询，我需要运行它 24 次，每次都使用我需要能够提供的特定日期(这些日期将是动态的)我想要避免重复我的查询 24 次，并加入它们，好像我需要回来添加额外的列一样，这将非常耗时.

I have a large query that accepts a 'to date' parameter, I need to run it 24 times, each time with a specific to date which I need to be able to supply (these dates are going to be dynamic) I want to avoid repeating my query 24 times with union alls joining them as if I need to come back and add additional columns it would be very time consuming.

推荐答案

SQL 主要是一种面向集合的语言 - 在其中使用循环通常是一个坏主意.

SQL is primarily a set-orientated language - it's generally a bad idea to use a loop in it.

在这种情况下，使用递归 CTE 可以获得类似的结果:

In this case, a similar result could be achieved using a recursive CTE:

with cte as
(select 1 i union all
 select i+1 i from cte where i < 5)
select dateadd(d, i-1, '2010-01-01') from cte

这篇关于SQL Server FOR EACH 循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！