mysql8.0递归使用之生成日历结构

mysql8.0递归使用之生成日历结构
recursive

with recursive d(ymd) as (
select SUBDATE(CURRENT_DATE+1,DAYOFMONTH(CURRENT_DATE))
union all
select ADDDATE(ymd,1)
from d
where ymd<LAST_DAY(CURRENT_DATE)
)
– select * from d;

,
d2 as (
select ymd,WEEK(ymd,1) wn, DAYOFWEEK(ymd) wd, DAYOFMONTH(ymd) wm from d
)
– select * from d2;
select
min(case wd when 2 then wm end) as ‘星期一’,
min(case wd when 3 then wm end) as ‘星期二’,
min(case wd when 4 then wm end) as ‘星期三’,
min(case wd when 5 then wm end) as ‘星期四’,
min(case wd when 6 then wm end) as ‘星期五’,
min(case wd when 7 then wm end) as ‘星期六’,
min(case wd when 1 then wm end) as ‘星期日’
from d2 group by wn;