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-%d' )

) tempAllTable

GROUP BY

dateTime

ORDER BY

dateTime DESC

查询结果展示：

#{date1} 该字段为创建临时表的起始时间。DATE_ADD(date，INTERVAL expr type)INTERVAL +1 DAY表示起始日期添加1天。

*创建临时表，是以业务表为基础创建的临时表。例子中message_record有很多数据，是为了省事创建。如果业务表没有数据，这里创建日期临时表为空。如果只有10条数据，那么临时表中也只有10条数据。

select [email protected] := DATE_ADD( @cdate, INTERVAL - 1 DAY ) dateTime 表示从临时表中查询每次减1天的日期。

#{date2} 该字段为临时表查询的截止日期，即每次减1天，到#{date2}日期截止。

#{date3}、#{date4} 为业务表中判断时间段的起始和结束时间。

一般情况补全天的话。临时表起始日期#{date1} = 业务表截止日期#{date4}  。临时表截止日期#{date2} = 业务表起始日期#{date3}。

success和fail数据是根据状态做的分类统计。如果不需要分类，只需要统计数量，这里可以替换count()函数

二、查询每月的数据，没有数据的月份，补全0：

SELECT

dateTime,

MAX( success ) AS success,

MAX( fail ) AS fail

FROM

(

SELECT

DATE_FORMAT( @cdate := DATE_ADD( @cdate, INTERVAL - 1 MONTH ), '%Y-%m' ) dateTime,

0 AS success,

0 AS fail

FROM

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

UNION ALL

SELECT

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

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

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

FROM

table t1

LEFT JOIN table t2 mr ON t1.id = t2.t1_id

WHERE

t1.time_create >= #{date2}

AND t1.time_create <= #{date3}

GROUP BY

DATE_FORMAT( t1.time_create, '%Y-%m' )

) tempAllTable

GROUP BY

dateTime

ORDER BY

dateTime DESC

查询结果展示：

#{date1} 类似于查询天中的起始时间，时间间隔为 1 MONTH。

#{date2}、#{date3}为业务表查询比较日期。

#{limit} 为查询结果中限制数量。这个条件也可以在查询天的SQL中添加。这里添加#{limit}是为了查询月数据准确。不加limit，[email protected]< #{date} 的话，这里比较的是天。但是临时表计算日期是INTERVAL -1 MONTH。如图：