MySQL 如何填充日期范围中的缺失日期？

在MySQL中，填充日期范围中的缺失日期是一个常见的需求。举个例子，如果您需要在一组日期范围内计算某个项目的数量，有可能会出现日期范围中某些日期没有项目数量的情况。这时候，您就需要填充这些缺失的日期。本文将介绍在MySQL中填充日期范围中的缺失日期的方法。

阅读更多：MySQL 教程

方法一：生成日期序列表并JOIN

首先，我们需要生成一个日期序列表，然后将其与实际存在的数据表进行JOIN操作，即可填充缺失的日期。

生成日期序列表的方法如下：

SELECT DATE_ADD('2000-01-01', INTERVAL seq.seq DAY) AS date
FROM (SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS digit_1
CROSS JOIN (SELECT 0 AS seq UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90) AS digit_2
CROSS JOIN (SELECT 0 AS seq UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900) AS digit_3
WHERE DATE_ADD('2000-01-01', INTERVAL seq.seq DAY) between '2022-01-01' and '2022-01-10';

该方法的原理是利用一些数字表，生成一个由数字序列对应的日期序列表。这里生成的日期序列表以'2022-01-01'和'2022-01-10'为例。

接下来，我们可以将该序列表与实际存在的数据表进行JOIN操作，来填充缺失日期。

SELECT dates.date, IFNULL(data.num, 0) AS num
FROM
  (SELECT DATE_ADD('2022-01-01', INTERVAL seq.seq DAY) AS date
  FROM (SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS digit_1
  CROSS JOIN (SELECT 0 AS seq UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90) AS digit_2
  CROSS JOIN (SELECT 0 AS seq UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900) AS digit_3
  WHERE DATE_ADD('2022-01-01', INTERVAL seq.seq DAY) between '2022-01-01' and '2022-01-10') AS dates
LEFT JOIN data ON dates.date = data.date;

在这个查询语句中，我们将生成的日期序列表命名为dates，将实际存在的数据表命名为data，然后使用LEFT JOIN将它们JOIN在一起，来填充缺失日期。

方法二：使用MySQL内置函数

除了生成日期序列表并JOIN之外，MySQL还有一些内置函数可以用来填充缺失的日期。

首先，我们要创建一个包含所有日期的子查询：

SELECT DATE_ADD('2022-01-01', INTERVAL seq.seq DAY) AS date
FROM (
  SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT
  4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS digit_1
CROSS JOIN (
  SELECT 0 AS seq UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT
  40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90
) AS digit_2
CROSS JOIN (
  SELECT 0 AS seq UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 UNION ALL SELECT
  400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
) AS digit_3
WHERE DATE_ADD('2022-01-01', INTERVAL seq.seq DAY) BETWEEN '2022-01-01' AND '2022-01-10';

然后，我们可以使用MySQL内置函数DATE_SUB()和DATE_ADD()来填充缺失的日期：

SELECT
  dates.date,
  COALESCE(data.num,0) as num
FROM (
  SELECT DATE_SUB(date('2022-01-01'), INTERVAL t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i DAY) AS date
  FROM (
      SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) AS t0,
  (
      SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) AS t1,
  (
      SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) AS t2,
  (
      SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) AS t3,
  (
      SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) AS t4
  WHERE date_sub(date('2022-01-01'), INTERVAL t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i DAY) BETWEEN '2022-01-01' AND '2022-01-10'
) AS dates
LEFT JOIN (
  SELECT date,num from data WHERE date BETWEEN '2022-01-01' AND '2022-01-10' 
) as data
ON dates.date=data.date;

在这个查询语句中，我们将包含所有日期的子查询命名为dates，将实际存在的数据表命名为data，然后使用LEFT JOIN将它们JOIN在一起，来填充缺失日期。其中COALESCE()函数用来避免出现NULL值。