统计查询某段时间内记录条数,当天没有记录时,自动填充为0.
SELECT
t1.days AS date,
IFNULL( t2.count, 0 ) count
FROM
(
SELECT
@cdate := date_add( @cdate, INTERVAL - 1 DAY ) days
FROM
( SELECT @cdate := DATE_ADD( '2020-02-20', INTERVAL + 1 DAY ) FROM check_list ) t0
) t1
LEFT JOIN ( SELECT STR_TO_DATE( DATE_FORMAT( create_date, '%Y-%m-%d' ), '%Y-%m-%d' ) date, count( 1 ) count FROM check_list GROUP BY date ) t2 ON t2.date = t1.days
WHERE
t1.days >= '2020-01-20'
ORDER BY
t1.days