最近在项目中前端用 echart 画折线图,用 sql 语句查询出来的日期不连续,如果数据表里缺少某一天的数据,就会导致那天的记录丢失,但显示的时候还是需要那天的数据,没有的话,可以默认为 0,想到了一种解决办法:

首先生成某个范围的日历表数据,再通过这个日历表连接查询需要统计数据表的数据,没有的话,默认为 0;这样就可以得出这个范围内完整的数据了,这种方案,适合项目这种需求的变化

现在通过如下 sql 语句生成日历表:

drop table if exists num;
CREATE TABLE num
(
    i INT
);-- 创建一个表用来储存0-9的数字
INSERT INTO num (i)
VALUES (0),
       (1),
       (2),
       (3),
       (4),
       (5),
       (6),
       (7),
       (8),
       (9);-- 生成0-9的数字,方便以后计算时间
drop table if exists xc_calendar;
CREATE TABLE IF NOT EXISTS xc_calendar
(
    day DATETIME
);
-- 生成一个存储日期的表,date是字段名
-- 这里是生成并插入日期数据
INSERT INTO xc_calendar(day)
SELECT ADDDATE
           ( -- 起始日期,从2017-1-1开始
               (DATE_FORMAT('2017-01-01 00:00:00', '%Y-%m-%d 00:00:00')), numlist.id
           ) AS `date`
FROM (SELECT n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000 AS id
      FROM num n1
               CROSS JOIN num AS n10
               CROSS JOIN num AS n100
               CROSS JOIN num AS n1000
     ) AS numlist;
drop table num;

执行 sql 后生成 1W 条记录,从2017-01-01 00:00:00~2044-05-09 00:00:00,这些数据已经足够用很久了;也可以通过修改相对应的条件,生成更多的数据,这里就不详细说明了,根据需求修改就行。

我们先来看下'2019-06-01' 到 '2019-06-31 23:59:59'之间的数据

SELECT imp.id, imp.dataTime
FROM ssp_importrecord AS imp
WHERE imp.importTime BETWEEN '2019-06-01' AND '2019-06-31 23:59:59'
ORDER BY imp.dataTime ASC

只有三天的数据,我们要将6月份其余的时间补齐

MySQL中实现日期自动补全

执行如下sql语句:

SELECT c.day AS day, result.*
FROM xc_calendar AS c
         LEFT JOIN (
    SELECT imp.id, imp.dataTime
    FROM ssp_importrecord AS imp
    WHERE imp.importTime BETWEEN '2019-06-01' AND '2019-06-31 23:59:59'
    ORDER BY imp.dataTime ASC
) AS result ON c.day = result.dataTime
WHERE c.day BETWEEN '2019-06-01' AND '2019-06-31 23:59:59'
GROUP BY c.day, result.id, result.dataTime
ORDER BY c.day ASC

查询结果如下:

MySQL中实现日期自动补全