MySQL日期统计查询时部分日期缺失解决方法

• 情景： 之前实现的时间间隔通过mysql.help_topic方法查询出连续的日期，中途遇到客户修改需求增加了时间间隔导致help_topic表实现时间递增的方式时间缺少。
• 原因： 通过分析发现，help_topic中最大值只能到659如果间隔时间超过此方法则会导致时间缺少。

mysql.help_topic方法具体实现： mysql.help_topic表中的 help_topic_id字段是一个递增的值，最大值为659，做统计查询的时候需要注意如果你的连续日期超过了659的话是不适合使用这种方法哟(一般统计查询都是 7天、30天..很少会超出659)

利用help_topic_id自增的特性我们可以生成一段连续的日期

SELECT date_format( date_add( '2021-12-08', INTERVAL + t.help_topic_id DAY ), '%Y-%m-%d' ) AS 'date' FROM mysql.help_topic t WHERE t.help_topic_id <= timestampdiff(DAY, '2021-12-08', '2021-12-14');

• 解决方法：

1. 创建一张临时表，在表中插入序号数据，该表的最大数量决定统计返回的最大条数

CREATE TABLE `sys_redundancy` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT COMMENT '序号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COMMENT='用于递增次数';

DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=1000 DO
INSERT INTO sys_redundancy(id) VALUES(i);
SET i = i+1;
END WHILE;
END $
DELIMITER ;

CALL proc_initData();

2. 根据新建表的自增字段完成时间统计

select * from(
SELECT
    IFNULL(count(a.user_id), 0) AS sum,
    b.tdate AS date
FROM
    sys_user a
RIGHT JOIN (
    SELECT
        DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL n.id-1 MONTH),'%Y-%m') AS tdate
    FROM
        sys_redundancy n 
) b ON DATE_FORMAT(a.create_time,'%Y-%m') = b.tdate and a.user_type = 2 
GROUP BY
    b.tdate
ORDER BY b.tdate desc
limit 12) m 
ORDER BY m.date asc;