mysql24小时数据没有补0

一、当天24小时没有补0

指的是从今天2021-01-07 00:00:00 到 2021-01-07 23:59:59
原始数据库表结构为：

SELECT a.hour HOUR, IFNULL(b.count, 0) COUNT FROM 
 (
    SELECT 0 HOUR UNION ALL SELECT 1 HOUR UNION ALL SELECT 2 HOUR UNION ALL SELECT 3  HOUR UNION ALL 
    SELECT 4 HOUR UNION ALL SELECT 5 HOUR UNION ALL SELECT 6 HOUR UNION ALL SELECT 7 HOUR UNION ALL 
    SELECT 8 HOUR UNION ALL SELECT 9 HOUR UNION ALL SELECT 10 HOUR UNION ALL SELECT 11 HOUR UNION ALL 
    SELECT 12 HOUR UNION ALL SELECT 13 HOUR UNION ALL SELECT 14 HOUR UNION ALL SELECT 15 HOUR UNION ALL 
    SELECT 16 HOUR UNION ALL SELECT 17 HOUR UNION ALL SELECT 18 HOUR UNION ALL SELECT 19 HOUR UNION ALL 
    SELECT 20 HOUR UNION ALL SELECT 21 HOUR UNION ALL SELECT 22 HOUR UNION ALL SELECT 23 HOUR
  ) a LEFT JOIN
  (
    SELECT
      HOUR(visit_time)  HOUR,
      COUNT(visit_time) COUNT
    FROM t_test
    WHERE DATE_FORMAT(visit_time, '%Y-%m-%d') = CURRENT_DATE
    GROUP BY DATE_FORMAT(visit_time, '%Y%m%d-%H'), HOUR 
  ) b 
  ON a.hour=b.hour 
ORDER BY HOUR

结果：

二、近24小时补0

指的是当前时间 2021-01-07 15:28:45 到 2022-01-06 16:28:45

SELECT
   t1. HOUR HOUR,
   COUNT(t2. HOUR) COUNT
FROM
   (
      SELECT
         DATE_FORMAT(
            @cdate := DATE_ADD(@cdate, INTERVAL - 1 HOUR),
            '%y-%m-%d %H'
         ) HOUR
      FROM
         (
            SELECT
               @cdate := DATE_ADD(
                  DATE_FORMAT(NOW(), '%y-%m-%d %H'),
                  INTERVAL + 1 HOUR
               )
            FROM
               information_log
         ) t0
      LIMIT 24
   ) t1
LEFT JOIN (
   SELECT
      DATE_FORMAT(visit_time, '%y-%m-%d %H') HOUR
   FROM
      information_log
   WHERE
      visit_time >= (NOW() - INTERVAL 24 HOUR)
) t2 ON t1. HOUR = t2. HOUR
GROUP BY
   t1. HOUR
ORDER BY
   t1. HOUR DESC

结果：

扩展：

需求1：

按照天数统计每天的总数，如果其中有几天没有数据，那么group by 返回会忽略那几天，如何填充0？
若统计7天的数据，其中只有2022年12月28日有数据，这样返回，数据只有1个，不符合报表统计的需求。期望没有值填0

SQL：

SELECT
   t1.`day`,
   COUNT(t2.information_id) payment_num
FROM
   (
     SELECT
       @cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY
     FROM
       (SELECT
           @cdate := DATE_ADD('2022-01-01', INTERVAL + 1 DAY)
        FROM information_log) t0
     LIMIT 7) t1
  LEFT JOIN
    (SELECT
       DATE(a.visit_time) DAY,
       a.information_id
     FROM
       information_log a
     JOIN information_management b
        ON a.information_id = b.id
     WHERE 
         DATE(visit_time) <= CURDATE()
        AND DATE(visit_time) > DATE_SUB('2022-01-01', INTERVAL 7 DAY)
      ) t2
    ON t2.day = t1.day
GROUP BY t1.`day`;

结果：

需求2：

获取近7天连续天数：

 SELECT
   @cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY
 FROM
   (SELECT
       @cdate := DATE_ADD(CURDATE(), INTERVAL + 1 DAY)
    FROM information_log) t0
 LIMIT 7

结果：

方法技能：

SQL分析：
左边操作：

1.@cdate := 是定义名为cdate的变量并赋值（select 后面必须用：=）

2.@cdate := DATE_ADD(‘20171219’, INTERVAL + 1 DAY) 按照传入的日期’20171219’，加一天

3.SELECT @cdate := DATE_ADD(‘20171219’, INTERVAL + 1 DAY) FROM information_log 找一张表记录肯定大于10条的即可，执行结果如下：

4.@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY 把定义的cdate变量天数-1（自减）

5.LIMIT 7 限制一下条数，大功告成，我们得到了指定日期往前7天的记录

右边操作：

left join group by t1.day

• 即按照左表关联业务数据，根据左表的日期分组，即分成了指定的7天数据，有记录就统计条数，没有记录就是0