使用递归CTE获取给定月份和月份之间逗号分隔的月份使用递归CTE

Question

我们有要求提供2个月和2年作为输入的要求。结果应该提供从月份到月份之间所有月份的逗号分隔的月份。

说，我们

From Month is 'Jan' 
From Year is 2011 
To Month is 'Apr' 
To Year is 2011 

所需的输出

Jan'11,Feb'11,Mar'11,Apr'11 

可以这样使用递归CTE来实现？我们需要使用递归CTE来实现这一点。有人能帮我解决这个问题吗？

Answer 1

试试这个代码：

--declaration of variables 
declare @frommonth char(3) = 'jan',@fromyear char(4) = 2011, 
     @tomonth char(3) = 'APR', @toyear char(4) = 2011 

declare @output varchar(max) 
declare @f int, @t int 

select --setting from and to month as months after 1900-01-01 
@f = datediff(month, 0, cast('1' [email protected][email protected] as datetime)), 
@t = datediff(month, 0, cast('1' [email protected][email protected] as datetime)) 

-- recusive loop 
;with cte as 
(
select @f m 
union all 
select m + 1 from cte 
where m < @t 
) 
select @output = coalesce(@output +',', '') +stuff(convert(varchar(11),dateadd(mm, m, 0), 109), 4, 6, '''') FROM CTE 

select @output 

结果：

Jan'11,Feb'11,Mar'11,Apr'11 

测试在这里：

http://data.stackexchange.com/stackoverflow/q/114801/declaration-of-variables