第一步: 先查询出1970年1月1日之后的XXX天的每个月的第一天的日期
主要用到了 adddate(date,INTERVAL expr type)
这一函数, 详情请参见: MySQL DATE_ADD() 函数
select adddate('1970-01-01', interval
t2.i * 100 + t1.i * 10 + t0.i month) `date`
from (select 0 i union select 1 union
select 2 union select 3 union
select 4 union select 5 union
select 6 union select 7 union
select 8 union select 9) t0,
(select 0 i union select 1 union
select 2 union select 3 union
select 4 union select 5 union
select 6 union select 7 union
select 8 union select 9) t1,
(select 0 i union select 1 union
select 2 union select 3 union
select 4 union select 5 union
select 6 union select 7 union
select 8 union select 9) t2
查询结果如下:
第二步: 使用where限制日期
select *
from (select adddate('1970-01-01', interval
t2.i * 100 + t1.i * 10 + t0.i month) `date`
from (select 0 i union select 1 union
select 2 union select 3 union
select 4 union select 5 union
select 6 union select 7 union
select 8 union select 9) t0,
(select 0 i union select 1 union
select 2 union select 3 union
select 4 union select 5 union
select 6 union select 7 union
select 8 union select 9) t1,
(select 0 i union select 1 union
select 2 union select 3 union
select 4 union select 5 union
select 6 union select 7 union
select 8 union select 9) t2) a
where `date` between date('2020-1-1') and now();
即可查询出 2020年1月1日
到现在每个月的第一天的日期, 结果如下图所示
最后, 格式化日期即可
select date_format(`date`, '%Y-%m') yearAndMonth
from (select adddate('1970-01-01', interval
t2.i * 100 + t1.i * 10 + t0.i month) `date`
from (select 0 i union select 1 union
select 2 union select 3 union
select 4 union select 5 union
select 6 union select 7 union
select 8 union select 9) t0,
(select 0 i union select 1 union
select 2 union select 3 union
select 4 union select 5 union
select 6 union select 7 union
select 8 union select 9) t1,
(select 0 i union select 1 union
select 2 union select 3 union
select 4 union select 5 union
select 6 union select 7 union
select 8 union select 9) t2) a
where `date` between date('2020-1-1') and now();
结果如下图:
参考资源:
[1] generate days from date range