mysql 按照天数进行查询,并补全没有的日期
直接上代码
select
days.v_time,
ifnull(datas.v_count, 0) as v_count
from
(
-- 数据查询
select
date(tfi.create_time) as v_time, count(1) as v_count
from
t_fault_info tfi
where
tfi.create_time <= '2020-08-10'
and tfi.create_time >= '2020-07-10'
group by
date(tfi.create_time)
order by
date(tfi.create_time))datas
-- 右连接
right join (
-- 日期查询
select
@date := date_add(@date, interval 1 day) as v_time
from
(
select
@date := date_add('2020-07-10', interval -1 day)
from
t_fault_info tfi ) days
where
@date <= date_add('2020-08-10', interval -1 day)
and @date >= date_add('2020-07-10', interval -1 day)) days on
datas.v_time = days.v_time
order by
days.v_time;
查询结果:
ps: 该方法有局限性,查询的最大天数被t_fault_info,即右连接表的大小限制了。如果查询天数不多,可以考虑用多表连接,笛卡尔乘积,来确定最大查询天数,如:
select * from (select 1 union select 2 union select 3) t1,(select 1 union select 2 union select 3) t2;
这个例子能查询的最大天数为3*3=9。
如果需要查询的天数很多,还是在JAVA代码里补全比较好。