MySQL按天分组统计一定时间内的数据，没有数据补0

简介

       工作中偶尔会出现一个查询数据的需求，那就是需要按天统计近一个月或其它一段时间内每天的所有记录或者分组数据，没有数据则自动补0。

       一般情况下我们都会过滤没有数据的时间，但前端拿到后端返回的数据想直接展示连续性数据变化时要进行再处理，下面介绍如何通过sql语句直接满足需求。

思路分析

1. 如果能每天都有数据，可以直接使用最简单的查询节省时间。
2. 要能够展示每天的日期就要创建一张日期虚拟表作为连接表。
3. 要使每天数据不为null，使用IFNULL(count,0)函数进行判断是否补零。
   
   

SQL实现

下面以查询近一个月每天的数据为示例展示SQL实现。

按天统计数据

1.没数据的一天过滤

SELECT DATE(CREATE_DATE) as date, COUNT(1) as count
FROM 表
WHERE 字段 = '1'
  AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d') >=
      DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d')
GROUP BY DATE(CREATE_DATE);

查询结果：

2.没数据的一天有日期总数为null

SELECT DATE_FORMAT(date,'%Y-%m-%d') AS date,data.num AS count
FROM (
         SELECT @days := DATE_ADD(@days, INTERVAL - 1 DAY) AS date
         FROM (SELECT @days := DATE_ADD(CURDATE(), INTERVAL + 1 DAY)
               FROM 表
              ) day
         WHERE DATE_FORMAT(@days, '%Y-%m-%d') >=
               DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 29 DAY), '%Y-%m-%d')
         ORDER BY date
     ) dates
         LEFT JOIN (
    SELECT  COUNT(1) AS num, DATE(CREATE_DATE) AS time
    FROM audit_work_sheet
    WHERE 字段 = '1'
      AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d %H:%i:%S') >=
          DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d %H:%i:%S')
    GROUP BY DATE(CREATE_DATE)
) data ON DATE(time) = date
ORDER BY date;

查询结果：

3.没数据的一天有日期总数为0

SELECT DATE_FORMAT(date,'%Y-%m-%d') AS date,IFNULL(data.num, 0) AS count
FROM (
         SELECT @days := DATE_ADD(@days, INTERVAL - 1 DAY) AS date
         FROM (SELECT @days := DATE_ADD(CURDATE(), INTERVAL + 1 DAY)
               FROM 表
              ) day
         WHERE DATE_FORMAT(@days, '%Y-%m-%d') >=
               DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 29 DAY), '%Y-%m-%d')
         ORDER BY date
     ) dates
         LEFT JOIN (
    SELECT  COUNT(1) AS num, DATE(CREATE_DATE) AS time
    FROM audit_work_sheet
    WHERE 字段 = '1'
      AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d %H:%i:%S') >=
          DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d %H:%i:%S')
    GROUP BY DATE(CREATE_DATE)
) data ON DATE(time) = date
ORDER BY date;

查询结果

4.加入其它分组字段没数据的一天有日期总数为0

SELECT DATE_FORMAT(date,'%Y-%m-%d') AS date,data.level AS level, IFNULL(data.num, 0) AS count
FROM (
         SELECT @days := DATE_ADD(@days, INTERVAL - 1 DAY) AS date
         FROM (SELECT @days := DATE_ADD(CURDATE(), INTERVAL + 1 DAY)
               FROM 表
              ) day
         WHERE DATE_FORMAT(@days, '%Y-%m-%d') >=
               DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 29 DAY), '%Y-%m-%d')
         ORDER BY date
     ) dates
         LEFT JOIN (
    SELECT RISK_LEVEL AS level, COUNT(1) AS num, DATE(CREATE_DATE) AS time
    FROM audit_work_sheet
    WHERE 字段 = '1'
      AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d %H:%i:%S') >=
          DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y-%m-%d %H:%i:%S')
    GROUP BY DATE(CREATE_DATE),level
) data ON DATE(time) = date
ORDER BY date,level;

查询结果

(中间过多数据不展示)

以上就是按天统计数据没有数据补零的SQL语句，按月按年等其它时间段，或其它分组条件，都可以直接修改参数后查询出结果。