1 背景
由于业务需求，在项目的报表中心中需要未来7天、按月统计数据，且要求按天补全数据，补数据填为0。附实测SQL语句，请大家指正。
2 举例
2.1未来7天，按天补全数据，无数据填充0
sql语句：
select t1.lastDays as x, IFNULL(t2.count,0) as val from (SELECT date_format( @lastDay := date_add( @lastDay, INTERVAL 1 DAY ), '%Y-%m-%d' ) lastDays FROM ( SELECT @lastDay := date_add( curdate( ), INTERVAL - 1 DAY ) FROM mysql.help_topic LIMIT 7 ) a)as t1 left join (select DATE_FORMAT(maintenance_time, '%Y-%m-%d') as time_1, SUM(b.employee_number) as count from maintenance_plan a LEFT JOIN maintenance_strategy b ON (b.model_id = a.model_id) where a.maintenance_type=1 group by maintenance_time)t2 on t2.time_1=t1.lastDays
查询结果：
2.2 当月1-月底，按天补全数据，无数据填充0
sql语句：
select t1.lastDays as x, IFNULL(t2.count,0) as val from (SELECT date_format(date_add(@firstDayOfMonth := DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY, '%Y-%m-01'), INTERVAL a.help_topic_id DAY), '%Y-%m-%d') AS lastDays FROM mysql.help_topic a WHERE a.help_topic_id BETWEEN 0 AND DATEDIFF(@lastDayOfMonth := LAST_DAY(CURDATE()), @firstDayOfMonth := DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY, '%Y-%m-01')))as t1 left join (select DATE_FORMAT(maintenance_time, '%Y-%m-%d') as time_1, COUNT(x.id) as count from maintenance_plan x group by DATE_FORMAT(x.maintenance_time, '%Y-%m-%d'))t2 on t2.time_1=t1.lastDays
查询结果：还有2024-05-01和2024-05-02
3 SQL语句原理和拆解说明
原理：使用MySql的系统表help_topic，使用表中自增ID（help_topic_id），用于进行行表关联操作和计数。使用sql语句查询，发现help_topic累计有701条数据（包括help_topic_id=0），MySql版本为：8.0.27
3.1 拆解思路
3.1.1 第一步：通过help_topic取连续的数值段
查询未来7天SQL语句，如下：
SELECT date_format( @lastDay := date_add( @lastDay, INTERVAL 1 DAY ), '%Y-%m-%d' ) lastDays FROM ( SELECT @lastDay := date_add( curdate( ), INTERVAL - 1 DAY ) FROM mysql.help_topic LIMIT 7 ) a
date_add( @lastDay, INTERVAL 1 DAY ) 表示每次增加1天，
date_add( curdate( ), INTERVAL - 1 DAY ) 表示包括当天
LIMIT 7 表示取7条记录
执行结果：
查询当月每天SQL语句，如下：
SELECT date_format(date_add(@firstDayOfMonth := DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY, '%Y-%m-01'), INTERVAL a.help_topic_id DAY), '%Y-%m-%d') AS lastDays FROM mysql.help_topic a WHERE a.help_topic_id BETWEEN 0 AND DATEDIFF(@lastDayOfMonth := LAST_DAY(CURDATE()), @firstDayOfMonth := DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY, '%Y-%m-01'))
LAST_DAY(CURDATE()) 表示当前日期的当月最后一天
执行结果：
3.1.2 第二步：使用left join左连接业务表
有了第一步的日期字段，结合业务表时，需要使用left join进行左连接。
使用on，将业务表中日期字段格式化为%Y-%m-%d，与日期字段（lastDays）格式保持一致，并将统计的结果值命名为指定的字段名（count），用于使用别名t2.count取值。如下所示
select t1.lastDays as x, IFNULL(t2.count,0) as val from (SELECT date_format(date_add(@firstDayOfMonth := DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY, '%Y-%m-01'), INTERVAL a.help_topic_id DAY), '%Y-%m-%d') AS lastDays FROM mysql.help_topic a WHERE a.help_topic_id BETWEEN 0 AND DATEDIFF(@lastDayOfMonth := LAST_DAY(CURDATE()), @firstDayOfMonth := DATE_FORMAT(CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY, '%Y-%m-01')))as t1 left join (select DATE_FORMAT(maintenance_time, '%Y-%m-%d') as time_1, COUNT(x.id) as count from maintenance_plan x group by DATE_FORMAT(x.maintenance_time, '%Y-%m-%d'))t2 on t2.time_1=t1.lastDays
3.1.3 第三步：查询数据时，使用IFNULL校验数据值，当为NULL时，赋值为0
如果业务表中无该日期的数据，left join业务表中该日期的记录为null，故可以在查询结果中使用IFNULL校验t2.count，实现为null，则赋值为0
4 拓展
统计未来7个月，将DAY换成MONTH，例子如下：
