使用CTE递归的方式实现时间维度表

关于CTE递归的语法，本人也是刚刚接触到，在介绍这个生成时间维度表之前，我先简单介绍一下CTE递归的语法吧！以方便初学者更容易理解（老司机可以无视）。

废话不多说，直接上SQL：

with A as
    (
        select 1 NUM
        union all
        select NUM+1 from A
        where NUM<20
	)
    select * from A 
    Option(MAXRECURSION 100)  --设置最大的递归次数

这应该算是最简单的例子了吧，意思就是从1数到20，执行结果如下：

看到这里相信你对CTE递归的基本语法应该有所熟悉了哈~

下面再来一个只输出日期的SQL:

with CTE as
    (
        select cast('2000-1-1' as date) dt
        union all
        select dateadd(day,1,dt) from CTE
        where dt<getdate()
    )
    select * from CTE 
    Option(MAXRECURSION 20000)  

执行结果：

这段SQL将日期从2000-01-01 到当前日期，一天不差的输出出来了。

最后，在这基础之上，开始我们今天的正题：生成时间维度表吧！

with CTE as
    (
        select cast('2000-1-1' as date) DateID
			  ,year( cast('2000-1-1' as date)) [Year]
			  ,month(cast('2000-1-1' as date))[Month]
			  ,day(cast('2000-1-1' as date))[Day]
			  ,CASE DATEPART(QUARTER, cast('2000-1-1' as date))
				   WHEN 1 THEN N'第一季度'
				   WHEN 2 THEN N'第二季度'
				   WHEN 3 THEN N'第三季度'
				   ELSE N'第四季度' END AS [Quarter]			       
			  ,CASE DATEPART(W, cast('2000-1-1' as date))
				   WHEN 1 THEN N'星期一'
				   WHEN 2 THEN N'星期二'
				   WHEN 3 THEN N'星期三'
				   WHEN 4 THEN N'星期四'
				   WHEN 5 THEN N'星期五'
				   WHEN 6 THEN N'星期六'
				   ELSE N'星期日' END AS [WeekDay]	
			  ,N'第'+CAST( DATEPART(WEEK, cast('2000-1-1' as date)) AS VARCHAR(2))+'周' AS [Week]		   
        union all
        select dateadd(day,1,DateID)
			  ,year(dateadd(day,1,DateID))
			  ,month(dateadd(day,1,DateID))
			  ,day(dateadd(day,1,DateID))
			  ,CASE DATEPART(QUARTER,dateadd(day,1,DateID))
				   WHEN 1 THEN N'第一季度'
				   WHEN 2 THEN N'第二季度'
				   WHEN 3 THEN N'第三季度'
				   ELSE N'第四季度' END AS [Quarter]	
			  ,CASE DATEPART(W, dateadd(day,1,DateID))
				   WHEN 1 THEN N'星期一'
				   WHEN 2 THEN N'星期二'
				   WHEN 3 THEN N'星期三'
				   WHEN 4 THEN N'星期四'
				   WHEN 5 THEN N'星期五'
				   WHEN 6 THEN N'星期六'
				   ELSE N'星期日' END AS [Week]	
			  ,N'第'+CAST( DATEPART(WEEK, dateadd(day,1,DateID)) AS VARCHAR(2))+'周' AS [Week]	
		from CTE
        where DateID<cast(getdate() as date)
    )
    select * from CTE 
	Option(MAXRECURSION 7000) --设置最大的递归次数，如果没有这一段，默认最大递归100次

最后展示一下最终结果：

如果你觉得这些栏位不够，还需要其他栏位的，可以根据需要结合时间函数在这基础之上追加列。

有不明白的地方可以给我留言，或者加我VX:838599867,有问题或者新技术可以一起研究哈^_^