SQL - 创建月份和月份名称的第一天的临时表或 CTE

问题描述

我需要在 SQL Server 2012 环境中基于 2 个参数创建临时表或公用表表达式

I need to create a temp table or common table expression based on 2 paremters in a SQL Server 2012 environment

@calYear 
@currentYear 

所以如果 @calYear = 5 和 @currentYear='2014'我想生成一个从当前年份开始的 5 年临时表，其中有 4 列，如

so if @calYear = 5 and @currentYear='2014' I would like to generate a temp table of 5 years starting from current year with 4 columns like

YearDesc      MonthName     MonthNum     FirstDayOfMonth
2014          Jan           1            1/1/2014
2014          Feb           2            2/1/2014
2014          Mar           3            3/1/2014
...
...
...
2018          Oct           10           10/1/2018
2018          Nov           11           11/1/2018
2018          Dec           12           12/1/2018

是否可以有效地执行 Do While 循环?我将如何解释月份名称?我正在使用一个非常麻烦的 Do While 循环来迭代一年中的所有月份，然后迭代所有年份.

Is it possible to do a Do While loop efficently? How would I account for the month names?I'm using a really cumbersome Do While loop to iterate all the months of the year then iterate all the years.

推荐答案

使用递归 cte 的一种方法:

One way using a recursive cte:

declare @calYear int = 5, @currentYear char(4) = '2014'

;with cte (dt) as (
    select DATEFROMPARTS(@currentyear,1,1) dt
    union all
    select dateadd(month,1,dt) 
    from cte where dt < dateadd(year,@calyear,DATEFROMPARTS(@currentyear,1,1))
    )

select year(dt) YearDesc, datename(month, dt) MonthName, month(dt) MonthNum, dt FirstDayOfMonth 
from cte
order by dt 

或使用数字表:(在本例中为 master..spt_values)

Or using a numbers table: (in this case master..spt_values)

declare @calYear int = 5, @currentYear char(4) = '2014'

;with cte2 (dt) as (
    select dateadd(month,number,DATEFROMPARTS(@currentyear,1,1)) dt
    from master..spt_values where type = 'p'
    and number <= 12*@calYear
    )
select year(dt) YearDesc, datename(month, dt) MonthName, month(dt) MonthNum, dt FirstDayOfMonth 
from cte2
order by dt 

这篇关于SQL - 创建月份和月份名称的第一天的临时表或 CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！