需求
将指定时间内的数据进行统计,并将无数据的日期补充 0
task_logs 为目标表
实际数据
SELECT DATE_FORMAT(create_time,'%Y-%m-%d') create_time FROM task_logs GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d');
补全到目标时间后
SELECT
@cdate := date_add( @cdate, INTERVAL - 1 DAY ) AS date_str
FROM
( SELECT @cdate := date_add( DATE_FORMAT(create_time,'%Y-%m-%d'), INTERVAL + 1 DAY ) FROM task_logs ) tmp1
WHERE
@cdate > '2023-02-01';
最终统计
将统计数据与生成的时间数据进行关联查询
SELECT
date_str,
create_time,
IFNULL( tl.c, 0 ) count
FROM
(
SELECT
DATE_FORMAT( create_time, '%Y-%m-%d' ) create_time,
count( 1 ) c
FROM
task_logs
GROUP BY
DATE_FORMAT( create_time, '%Y-%m-%d' )) tl
RIGHT JOIN (
SELECT
@cdate := date_add( @cdate, INTERVAL - 1 DAY ) AS date_str
FROM
( SELECT @cdate := date_add( DATE_FORMAT( create_time, '%Y-%m-%d' ), INTERVAL + 1 DAY ) FROM task_logs ) tmp1
WHERE
@cdate > '2023-02-10'
) tl_temp ON date_str = DATE_FORMAT( create_time, '%Y-%m-%d' )
GROUP BY
date_str
ORDER BY
date_str DESC