MySQL中实现连续日期内数据统计,缺省天数0补全,sql实现

 

创建test表

CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `count` varchar(255) DEFAULT NULL,
  `create` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

sql语句

select ifnull(sum(u.count),0) as count,A.date from 
(
select date_add('2020-04-13',interval @i:=@i+1 day) as date 
from (
select 1 
union all select 1 
union all select 1
union all select 1 
union all select 1 
union all select 1 
union all select 1 
union all select 1 ) as tmp,
 (select @i:= -1) t

) A
left join test u on s.date = date_format(u.create,'%Y-%m-%d')
GROUP BY A.date

执行代码运行结果：

其中4月14,4月19,4月20是没有数据以0表示；

统计年，月同理，替换关键字day，如果年则是year，月month；

@i:=@i+1：Oracle中有一个伪列rownum，可以在生成查询结果表的时候生成一组递增的序列号。MySQL中没有这个伪列，但是有时候要用，可以用如下方法模拟生成一列自增序号。

ifnull ： 如果返回为null则用0代替；

coalesce(sum(count),0):如果返回为null则用0代替；

DATE_ADD() 函数向日期添加指定的时间间隔。

中间红色部分union all需要在java代码中生成，日期间隔多少他生成多少个