在做数据统计的时候,经常会遇到以每天,每月为周期的统计。有时候,某一天没有数据时按照group查询会导致数据为0的天数没被记录。下面提供一个方法思路,在使用mysql查询时,不需要额外建表即可以做到给结果集补0
1.不建表查询日期
SELECT
DATE_FORMAT( date_add(concat('2020-08-20'), interval(help_topic_id) DAY),'%Y-%m-%d') date
FROM
mysql.help_topic
WHERE
help_topic_id <= timestampdiff(DAY,concat('2020-08-20'),concat('2020-10-05'))
结果如下:
2. 分组数据统计
select
count(*) as all_num, DATE(update_time) as date
from
tb_flow
where
action='cell_detection' group by date
结果如下:
- 建立左连接
select
t1.date,
t2.all_num
from
(
SELECT
DATE_FORMAT(
date_add(
concat('2020-04-29'),
interval(help_topic_id) DAY
),
'%Y-%m-%d'
) date
FROM
mysql.help_topic
WHERE
help_topic_id <= timestampdiff(DAY, concat('2020-04-29'), concat('2020-10-05'))
) as t1
left join (
select
count(*) as all_num,
DATE(update_time) as date
from
tb_flow
where
action = 'cell_detection'
group by
date
) as t2 on t1.date = t2.date
结果如下:
- 加上if判断实现补0
select
t1.date,
if(t2.all_num is null, 0, t2.all_num) as all_num
from
(
SELECT
DATE_FORMAT(
date_add(
concat('2020-04-29'),
interval(help_topic_id) DAY
),
'%Y-%m-%d'
) date
FROM
mysql.help_topic
WHERE
help_topic_id <= timestampdiff(DAY, concat('2020-04-29'), concat('2020-10-05'))
) as t1
left join (
select
count(*) as all_num,
DATE(update_time) as date
from
tb_flow
where
action = 'cell_detection'
group by
date
) as t2 on t1.date = t2.date
结果如下: