我已经通过CTE生成了月份编号,并尝试生成如下输出1,2,3,....30,31
通过使用for xml
,但我得到了一个错误
这是我的完整脚本
;With CTEMonth As
(Select 1 As Number
Union All
Select m.Number + 1 As Number
From CTEMonth m
Where m.Number <=30)
DECLARE @cols AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + m.Number
from CTEMonth m
order by m.Number
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
print @cols
我的SQL出了什么问题?请引导。感谢
您需要将DECLARE移动到CTE之前的。
DECLARE @cols AS NVARCHAR(MAX);
;With CTEMonth As
(Select 1 As Number
Union All
Select m.Number + 1 As Number
From CTEMonth m
Where m.Number <=30)
select @cols = STUFF((SELECT ',' + m.Number
from CTEMonth m
order by m.Number
FOR XML PATH(''), TYPE
).value(N'./text()[1]', 'NVARCHAR(MAX)')
,1,1,'');
print @cols;
此外,递归CTE并不是生成集合的最有效方法——请参阅此处、此处和此处,以获得更好的替代方案,如数字表或日历表。