统计,补全日期
BBB表 可以是任何一个有数据的表,他的作用只是补日期用,但是如果数据行数小于天数 会缺少天数,比如 数据只有5条,想统计10天的,就会只补5天
select sum(count) as count, IF(date = CURDATE(), '今天', date_format(date, '%m-%d')) AS date
from (
select count(*) count, date_format(a.create_time, '%Y-%m-%d') date
from AAA a
where a.create_time >= #{startDate}
and a.create_time < #{endDate}
GROUP BY date
UNION ALL
select @uu := 0 as count, date
from (
select @num := @num + 1 as number,
DATE_ADD(#{startDate}, INTERVAL @num DAY) as date
from BBB a,
(select @num := -1) t
where DATE_ADD(#{startDate}, INTERVAL @num DAY) <
DATE_SUB(#{endDate}, INTERVAL 1 DAY)
) rr
) sss
GROUP BY sss.date
ORDER BY date