MySQL实现按天分组统计，无数据自动补0不创建日历表方案

1.大部分方案是直接生成日历表，然后leftjoin关联查询

2.有部分是按照数据库去生成日期，然后去生成数据，在使用中发现如下问题（直接沾代码）

SELECT t1.date_str , COALESCE(t2.date_total_count,0) as date_total_count
FROM(
SELECT @cdate:= date_add(@cdate,interval - 1 day) as date_str , 0 as date_count
FROM (SELECT @cdate:=date_add(CURDATE(),interval + 1 day) from sp_orders LIMIT 20) t1
where @cdate > '2021-01-01' 
) t1
LEFT JOIN(
SELECT FROM_UNIXTIME(m.order_time, '%Y-%m-%d') as date_str , m.pay_price as date_total_count
from sp_orders as m   where m.order_time  BETWEEN  1577860436 and 1585722836
group by FROM_UNIXTIME(m.order_time, '%Y-%m-%d')
) t2
on t1.date_str = t2.date_str

主要生成日期的代码：

SELECT @cdate:= date_add(@cdate,interval - 1 day) as date_str , 0 as date_count
FROM (SELECT @cdate:=date_add(CURDATE(),interval + 1 day) from sp_orders LIMIT 20) t1
where @cdate > '2021-01-01' 

指的是2021-01-01 到单前时间的日期，所以单独看是生成某天到今天得所有数据，没得自动补0，但是如何查询两个时间段所有的日期？

下面方案是生成两个时间段内，连续日期

function count_days($a,$b){

    $a_dt = getdate($a);

    $b_dt = getdate($b);

    $a_new = mktime(12, 0, 0, $a_dt['mon'], $a_dt['mday'], $a_dt['year']);

    $b_new = mktime(12, 0, 0, $b_dt['mon'], $b_dt['mday'], $b_dt['year']);

    return round(abs($a_new-$b_new)/86400);

}

生成时间天数$leftData，$a,$b 为时间戳 且$a>$b

SELECT @cdate:= date_add(@cdate,interval - 1 day) as date_str , 0 as date_count
FROM (SELECT @cdate:=date_add(FROM_UNIXTIME($b,'%Y-%m-%d'),interval + 1 day) from sp_orders LIMIT $leftData) t1
where @cdate >FROM_UNIXTIME($a,'%Y-%m-%d')

通过limit属性设置天数，已最大天数为基础，倒查天数

这样就完美解决了，赞贴下最终代码   生成时间天数$leftData，$a,$b 为时间戳 且$a>$b

SELECT t1.date_str , COALESCE(t2.date_total_count,0) as date_total_count
FROM(
SELECT @cdate:= date_add(@cdate,interval - 1 day) as date_str , 0 as date_count
FROM (SELECT @cdate:=date_add(FROM_UNIXTIME($b,'%Y-%m-%d %H:%i:%s'),interval + 1 day) from sp_orders LIMIT leftData) t1
where @cdate > FROM_UNIXTIME($a,'%Y-%m-%d %H:%i:%s')
) t1
LEFT JOIN(
SELECT FROM_UNIXTIME(m.order_time, '%Y-%m-%d') as date_str , m.pay_price as date_total_count
from sp_orders as m   where m.order_time  BETWEEN  1577860436 and 1585722836
group by FROM_UNIXTIME(m.order_time, '%Y-%m-%d')
) t2
on t1.date_str = t2.date_str

OK ,打完收工。