mysql group by日期 不存在的日期，统计数量为0

参考 https://blog.csdn.net/innerpeaceScorpio/article/details/53156485

需求：报表工具需要手动写sql，展示一周内的开单量

结果：需要按照日期范围返回开单量，如果该日期没有开单需要返回0

sql语句
从给定日期开始生成一周的日期虚拟列，做join返回结果

select date_add('2022-09-15',interval @i:=@i+1 day) as date 
    from (
        select 1 
        union all select 1 
        union all select 1 
        union all select 1 
        union all select 1 
        union all select 1 
        union all select 1 
    ) as tmp,
    (select @i:= -1) t

由于sql执行是通过其他微服务的mybatis执行，发现不支持@i:=@i+1写法

后来看到该文章https://blog.csdn.net/innerpeaceScorpio/article/details/53156485

用以下方式获取两个日期之间的所有日期

    select date_format(date,'%Y-%m-%d') date
from
(select adddate('2022-09-15',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) 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,
(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) t3
) c
where date between '2022-09-15' and '2022-09-21'

通过join获取结果

select Date_format(d.date,'%W') as week,IFNULL(Tt.count,0) count from 
(
    select date_format(date,'%Y-%m-%d') date
from
(select adddate('2022-09-15',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) 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,
(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) t3
) c
where date between '2022-09-15' and '2022-09-21'
)  d
left join(
select count(*) count,create_time from(
select create_time from 数据表  where create_time between'2022-09-15' and '2022-09-21'
where create_time between '2022-09-15' and '2022-09-21')  group by day(create_time)) Tt 
on DATE_FORMAT(Tt.create_time,'%Y-%m-%d') = d.date group by d.date order by d.date;