Mysql CTE 递归应用案例 创建时间维度表

CTE的递归功能很强大，可以有很多实际的运用场景。比如我们经常用到的日期维度表。这个日期维度表在PowerBI上很常见。以前可以在网上找日期维度表或者用EXCEL写公式生成。通过CTE的递归功能可以很快的生成。

可以自己定义开始时间和结束时间，非常的方面。我这里设置的是2020年1月1号开始到当前日期。完全可以根据实际需要自定义。

set @dt = '2020-1-1';

with recursive CTE as
(
select @dt as DateID
,year(@dt) as year_num
,month(@dt) as month_num
,day(@dt) as day_num
,CASE QUARTER(@dt)
WHEN 1 THEN N'Q1'
WHEN 2 THEN N'Q2'
WHEN 3 THEN N'Q3'
ELSE N'Q4' END AS Quarter_num
,CASE dayofweek(@dt)
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_num
,WEEK(@dt,1) AS Week_num
union all
select DATE_ADD(DateID,INTERVAL 1 DAY)
,year(DATE_ADD(DateID,INTERVAL 1 DAY)) 
,month(DATE_ADD(DateID,INTERVAL 1 DAY)) 
,day(DATE_ADD(DateID,INTERVAL 1 DAY))
,CASE QUARTER(DATE_ADD(DateID,INTERVAL 1 DAY))
WHEN 1 THEN N'Q1'
WHEN 2 THEN N'Q2'
WHEN 3 THEN N'Q3'
ELSE N'Q4' END AS Quarter_num
,CASE dayofweek(DATE_ADD(DateID,INTERVAL 1 DAY))
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_num
,WEEK(DATE_ADD(DateID,INTERVAL 1 DAY),1)
from CTE
where DateID<CURDATE()
)

SELECT * FROM CTE;

执行结果如下：