-- 本月至今
select @num:=@num+1,date_format(adddate(DATE_ADD(curdate(),interval -day(curdate()) day), INTERVAL @num DAY),'%Y-%m-%d') as date from table_name,(select @num:=0) t where adddate(DATE_ADD(curdate(),interval -day(curdate()) day), INTERVAL @num DAY) < date_format(curdate(),'%Y-%m-%d') order by date;
table_name 替换为数据库内任意记录超过31条的表名即可。
-- 当月按天
select @num:=@num+1,date_format(adddate('2020-02-01', INTERVAL @num-1 DAY),'%Y-%m-%d') as date from odr_poorder,(select @num:=0) t where adddate('2020-02-01', INTERVAL @num DAY) <= DATE_FORMAT('2020-02-28','%Y-%m-%d') order by date;
-- 当年按月
select @num:=@num+1,date_format(adddate('2020-01-01', INTERVAL @num-1 MONTH),'%Y-%m') as date from odr_poorder,(select @num:=0) t where adddate('2020-01-01', INTERVAL @num-1 MONTH) <= DATE_FORMAT('2020-12-01','%Y-%m') order by date;