通过用sql语句对表进行日期统计 12月10号到12月14号的数据，想知道每天的数据有多少
但是有一个问题是：如果我的数据中没有12月13号、12号的话，返回的结果中就会缺少这两天的记录。
如果没有日期的话应该给12号、13号补一条为0的记录
下面我来还原整个过程
一、查询数据库中的基础数据
首先通过最原始的方式查询一下数据库的数据信息
在执行结果中可以看到数据里面是没有
2021-12-12和2021-12-13日期的数据
SELECT
id,
issued_time AS create_time,
standard_cn_name
FROM standard
WHERE issued_status = 1;
执行结果
id create_time standard_cn_name
10 2021-12-10 09:42:32 汽运四级分类标准
14 2021-12-14 18:36:44 订单工艺标准
15 2021-12-08 10:30:27 订单工艺标准-1638879781693
28 2021-12-09 17:00:16 订单工艺路线0cp
56 2021-12-14 15:20:55 物料组222
91 2021-12-14 15:17:11 物料组333
93 2021-12-11 15:37:22 物料组444
二、统计每天的数量，日期进行date_format再group by
统计的结果中发现统计后没有12号和13号的数据，但是我们想要
mysql中能够补全这些缺失的日期，并且count为0，希望的预期结果展示在下面
SELECT
date_format( issued_time, '%Y-%m-%d' ) AS create_time,
count(*) AS count
FROM standard
WHERE issued_status = 1
GROUP BY date_format( issued_time, '%Y-%m-%d' )
执行结果
create_time count
2021-12-08 1
2021-12-09 1
2021-12-10 1
2021-12-11 1
2021-12-14 3
希望的预期结果
create_time count
2021-12-08 1
2021-12-09 1
2021-12-10 1
2021-12-11 1
2021-12-12 0
2021-12-13 0
2021-12-14 3
那我们有什么方式可以统计缺失的日期呢?
三、使用mysql.help_topic表查询出连续的日期
mysql.help_topic表中的 help_topic_id字段是一个递增的值，最大值为659，做统计查询的时候需要注意如果你的连续日期超过了659的话是不适合使用这种方法哟(一般统计查询都是 7天、30天..很少会超出659)利用help_topic_id自增的特性我们可以生成一段连续的日期
假如我们想生成 2021-12-08到2021-12-14的连续日期
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')
执行结果
date
2021-12-08
2021-12-09
2021-12-10
2021-12-11
2021-12-12
2021-12-13
2021-12-14
假如想生成连续的月份
SELECT
date_format( date_add( '2021-12-01', INTERVAL + t.help_topic_id month ), '%Y-%m') AS 'date'
FROM
mysql.help_topic t
WHERE
t.help_topic_id <= timestampdiff(month, '2021-12-01', '2022-5-01')
执行结果
date
2021-12
2022-01
2022-02
2022-03
2022-04
2022-05
二、通过mysql.help_topic生成的结果再关联我们的业务数据
使用mysql.help_topic生成连续日期
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')
执行结果
date
2021-12-08
2021-12-09
2021-12-10
2021-12-11
2021-12-12
2021-12-13
2021-12-14
使用分组查询业务数据
SELECT
date_format( issued_time, '%Y-%m-%d' ) AS create_time,
count(*) AS count
FROM standard
WHERE issued_status = 1
GROUP BY date_format( issued_time, '%Y-%m-%d' )
执行结果
create_time count
2021-12-08 1
2021-12-09 1
2021-12-10 1
2021-12-11 1
2021-12-14 3
现在是需要将 mysql.help_topic生成的连续日期和业务数据join关联结合
整合后的sql如下, t1表就是生成的连续日期，t2表是业务数据表， 通过格式化后的日期进行join关联
SELECT
date,
ifnull( count, 0 ) AS count
FROM
(
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' )
) t1
LEFT JOIN (
SELECT
date_format( issued_time, '%Y-%m-%d' ) AS create_time,
count(*) AS count
FROM
standard
WHERE
issued_status = 1
GROUP BY
date_format( issued_time, '%Y-%m-%d' )
) t2 ON t1.date = t2.create_time
执行结果
date count
2021-12-08 1
2021-12-09 1
2021-12-10 1
2021-12-11 1
2021-12-12 0
2021-12-13 0
2021-12-14 3
