MySQL按日期查询补0不建立表方法

在做数据统计的时候，经常会遇到以每天，每月为周期的统计。有时候，某一天没有数据时按照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

结果如下：

3. 建立左连接

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

结果如下：

4. 加上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

结果如下：