mysql两个日期的连续日期_mysql 构造连续的日期

需求，我想以 年-月-日的格式，统计自 2019-08-20日 前10天的记录数，如果该天没有任何一条记录，则给予0

原始数据-》我想要的结果集数据

   ==============》

1、测试数据

drop table if existstest2;create table test2(id int primary key auto_increment,curr_date datetime);insert into test2(curr_date) values('2019-08-11 10:12:30'),('2019-08-14 10:12:30'),('2019-08-16 10:12:30');

select * from test2;

问题1：如果我们直接格式化时间到  年-月-日 的格式，那么没有记录的天数就会被漏掉；

select date_format(curr_date,'%Y-%m-%d') as 'curr_date',

count(id) as 'record_count'

from test2 group by date_format(curr_date,'%Y-%m-%d');

结果是这样的，就没有达到我们的效果。

2、了解一个mysql数据库下的自带表

mysql.help_topic;

如下图，它有一个从0~657的顺序ID序列，一些短期的操作可以借助它来完成，比如生成连续的日期。

select max(help_topic_id),min(help_topic_id),count(help_topic_id) from mysql.help_topic;

3、生成连续的日期

(1)我们可以借助 2中的mysql.help_topic表，和一些固定的参数和时间函数配合即可

(2)我们也可以自己构造一个临时表，构造一个足够大的id顺序序列。

这个方法也很简单但是有点费事还要额外去生成，如果657个自增id序列已经够用推荐使用第1种(这种就不演示了，原理一样。)

--获取自'2019-08-20'的前10天

select date_format(date_add('2019-08-20',interval -t.help_topic_id day),'%Y-%m-%d') as 'curr_date'

from mysql.help_topic t where t.help_topic_id<=10;

这就出来了

4、最终聚合，完成需求

/*

--获取自某天开始的前10天

select date_format(date_add('2019-08-20',interval -t.help_topic_id day),'%Y-%m-%d') as 'curr_date' from mysql.help_topic t where t.help_topic_id<=10;--数据表根据时间 年-月-日 格式分组统计行数

select date_format(curr_date,'%Y-%m-%d') as 'curr_date',count(id) as 'record_count' from test2 group by date_format(curr_date,'%Y-%m-%d');

*/--最终整合

select t1.curr_date,ifnull(t2.record_count,0) as record_count from(select date_format(date_add('2019-08-20',interval -t.help_topic_id day),'%Y-%m-%d') as 'curr_date'

from mysql.help_topic t where t.help_topic_id<=10) t1left join(select date_format(curr_date,'%Y-%m-%d') as 'curr_date',count(id) as 'record_count'

from test2 group by date_format(curr_date,'%Y-%m-%d')

) t2on t1.curr_date=t2.curr_dateorder by t1.curr_date

最终聚合结果如下左图，数据表聚合如下右图，构造连续日期如下中间图

　　

 

如果自己构造中间表，只是把 mysql.help_topic 表 替换成你自己构造的顺序序列表中间表即可。