mysql 自动填充每周，每月，每年的日期查询为空补0

按天查询：

select * from 
(
  SELECT t1.id,@cdate := date_add(@cdate,interval - 1 day) date , 0 as sum
from (SELECT a.id,DATE_FORMAT(create_time,'%Y-%m-%d') as date,@cdate :=date_add(CURDATE(),interval + 1 day) from speak_ip_visit_record a)  t1 
where @cdate > "2020-03-26" order by @cdate
  
) a left join ( select *,DATE_FORMAT(create_time,'%Y-%m-%d') as date,count(ip_day) as ipCount
   from speak_ip_visit_record 
   group by ip_day) b on a.date = b.date  group by  a.date

按年查询：
select * from 
(
  SELECT t1.id, 0 as sum,DATE_FORMAT(@cdate := date_add(@cdate,interval - 1 month) ,'%Y-%m') as date
from (SELECT a.id,DATE_FORMAT(create_time,'%Y-%m') as date,@cdate :=date_add(CURDATE(),interval + 1 month) from speak_ip_visit_record a)  t1 
where @cdate > "2019-04" order by @cdate
  
) a left join ( select *,DATE_FORMAT(create_time,'%Y-%m') as date,count(ip_month) as ipCount
   from speak_ip_visit_record 
   group by ip_month) b on a.date = b.date  group by  a.date
     
  按小时查询：   
     select * from 
(
  SELECT t1.id, 0 as sum, DATE_FORMAT(@cdate := date_add(@cdate,interval - 1 hour) ,'%y-%m-%d %H') as date
from (SELECT a.id,DATE_FORMAT(create_time,'%y-%m-%d %H') as date,@cdate := DATE_ADD( DATE_FORMAT(NOW(), '%y-%m-%d %H'),  INTERVAL + 1 HOUR ) from speak_ip_visit_record a)  t1 
LIMIT 24
  
) a left join ( select *,DATE_FORMAT(create_time,'%y-%m-%d %H') as date,count(ip_hour) as ipCount
   from speak_ip_visit_record WHERE create_time>"2020-04-13 00:00:00"
   group by ip_hour,ip_day) b on a.date = b.date  group by  a.date