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

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

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

SELECTdateTime,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 ALLSELECTDATE_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 FROMtable t1LEFT JOIN table2 t2 ON t1.id = t2.t1_id ate_time >= #{date3}ate_time <= #{date4}GROUP BYDATE_FORMAT( t1.create_time, '%Y-%m-%d' ) ) tempAllTable 
GROUP BYdateTime
ORDER BYdateTime DESC

查询结果展示：

1. #{date1} 该字段为创建临时表的起始时间。DATE_ADD（date，INTERVAL expr type）INTERVAL +1 DAY表示起始日期添加1天。
2. *创建临时表，是以业务表为基础创建的临时表。例子中message_record有很多数据，是为了省事创建。如果业务表没有数据，这里创建日期临时表为空。如果只有10条数据，那么临时表中也只有10条数据。
3. select 中查询@cdate := DATE_ADD( @cdate, INTERVAL - 1 DAY ) dateTime 表示从临时表中查询每次减1天的日期。
4. #{date2} 该字段为临时表查询的截止日期，即每次减1天，到#{date2}日期截止。
5. #{date3}、#{date4} 为业务表中判断时间段的起始和结束时间。
6. 一般情况补全天的话。临时表起始日期#{date1} = 业务表截止日期#{date4}  。临时表截止日期#{date2} = 业务表起始日期#{date3}。
7. success和fail数据是根据状态做的分类统计。如果不需要分类，只需要统计数量，这里可以替换count()函数

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

SELECTdateTime,MAX( success ) AS success,MAX( fail ) AS fail 
FROM(SELECTDATE_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 ALLSELECTDATE_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 FROMtable t1LEFT JOIN table t2 mr ON t1.id = t2.t1_id WHEREt1.time_create >= #{date2} AND t1.time_create <= #{date3} GROUP BYDATE_FORMAT( t1.time_create, '%Y-%m' ) ) tempAllTable 
GROUP BYdateTime 
ORDER BYdateTime DESC

查询结果展示：

1. #{date1} 类似于查询天中的起始时间，时间间隔为 1 MONTH。
2. #{date2}、#{date3}为业务表查询比较日期。
3. #{limit} 为查询结果中限制数量。这个条件也可以在查询天的SQL中添加。这里添加#{limit}是为了查询月数据准确。不加limit，只有@cdate< #{date} 的话，这里比较的是天。但是临时表计算日期是INTERVAL -1 MONTH。如图：