最后查询结果
image.png
sql如下:
select t3.days, max(t3.num) num from (
SELECT @cdate := date_add(@cdate,interval -1 day) days, 0 as num from
(SELECT @cdate := DATE_ADD( '2020-07-31', INTERVAL + 1 day) from `mogu_blog`.`t_category_menu` limit 31 ) t1
UNION ALL
#以下为你所需要查询得业务表根据日期统计数据
select DATE_FORMAT(create_time, '%Y-%m-%d') as days,count(*) as num from `mogu_blog`.`t_category_menu` GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')
) t3 where t3.days between '2020-07-01' and '2020-07-31' GROUP BY t3.days desc
解析
构造所需要得日期
ps:需要数据中数据大于31条数据进行构造
SELECT @cdate := date_add(@cdate,interval -1 day) days from
(SELECT @cdate := DATE_ADD( '结束时间', INTERVAL + 1 day) from `mogu_blog`.`t_category_menu` limit 31 ) t1
image.png
