mysql 构造连续的日期

 

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

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

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

 

 

 

 

1、测试数据

drop table  if exists test2;
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  
) t1
left 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')     
) t2
on t1.curr_date=t2.curr_date
order by t1.curr_date

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

　　 

 

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