问题
在日常使用中,需要统计每日的数据。但是实际情况下会有部分日期无数据(为0),如果按照使用group by按日聚集数据会导致数据为0的日期遗漏。
表结构如下:
数据如下:
使用语句:
select
date_format(u.register_time, '%Y-%m-%d') as r_t , count(*)
as
num
from
`user` u
group by
r_t
查询结果如下:
可以看到查询结果中只有存在数据的天数,若该天无记录则无法统计。
解决方法
修改语句如下:
select t1.ev_day, ifnull(u1.num, 0) as num from
(
select date_format(date_sub('2022-08-17'/*结束日期*/,interval t.help_topic_id day),'%Y-%m-%d') as ev_day
from mysql.help_topic t,
(
select TIMESTAMPDIFF(day, '2022-08-10'/*开始日期*/, '2022-08-17'/*结束日期*/)+1 as subday
) as subdayt
where t.help_topic_id < subdayt.subday
) as t1 left join
(
select date_format(u.register_time, '%Y-%m-%d') as r_t , count(*) as num from `user` u group by r_t
) as u1 on u1.r_t = t1.ev_day
查询结果如下:
无记录的天数结果为0,解决问题!
该方法借助了help_topic 表的help_topic_id字段,由于help_topic_id字段只有七百条左右,所以若天数跨度超过该字段的记录数,可选用其他合适的表(要保证借助的字段数值连续!)