最后查詢結(jié)果
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
#以下為你所需要查詢得業(yè)務表根據(jù)日期統(tǒng)計數(shù)據(jù)
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
解析
構(gòu)造所需要得日期
ps:需要數(shù)據(jù)中數(shù)據(jù)大于31條數(shù)據(jù)進行構(gòu)造
SELECT @cdate := date_add(@cdate,interval -1 day) days from
(SELECT @cdate := DATE_ADD( '結(jié)束時間', INTERVAL + 1 day) from `mogu_blog`.`t_category_menu` limit 31 ) t1
image.png
