mysql 补全日期_MySQL查询一段时间内的数据，没有数据的日期补全0

一、查询每天的数据，没有数据的那天，补全0：

SELECT

dateTime,

MAX( success ) AS success,

MAX( fail ) AS fail

FROM

(

SELECT

@cdate := DATE_ADD( @cdate, INTERVAL - 1 DAY ) dateTime,

0 AS success,

0 AS fail

FROM

( SELECT @cdate := DATE_ADD( #{date1}, INTERVAL + 1 DAY ) FROM table ) temp

WHERE

@cdate > #{date2} UNION ALL

SELECT

DATE_FORMAT( t1.create_time, '%Y-%m-%d' ) AS dateTime,

sum( CASE WHEN t1.status = 1 THEN 1 ELSE 0 END ) AS success,

sum( CASE WHEN t1.status != 1 THEN 1 ELSE 0 END ) AS fail

FROM

table t1

LEFT JOIN table2 t2 ON t1.id = t2.t1_id

WHERE

t1.create_time >= #{date3}

and t1.create_time <= #{date4}

GROUP BY

DATE_FORMAT( t1.create_time, '%Y-%m-