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

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

使用递归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 cteorder by dt

或使用数字表：（在这种情况下为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 cte2order by dt