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

在MySQL中填充日期范围内的缺失日期

在某些情况下，您可能会遇到日期范围内的日期缺失的表。这可能会扰乱数据分析和绘图过程。为了弥补这一差距，MySQL 提供了一种解决方案，可以使用指定的数值填充缺失的日期。

让我们考虑一个记录与特定日期关联的分数的表的示例。如果省略某些日期，我们的目标是使用占位符值（例如 0）来完成它们，以获得连续的范围。

应对挑战

MySQL 缺乏原生递归功能，但我们可以利用 NUMBERS 表技巧根据自动递增列生成日期序列。

生成日期序列

1. 创建 NUMBERS 表：

<code class="language-sql">DROP TABLE IF EXISTS `numbers`;
CREATE TABLE `numbers` (
  `id` int(10) unsigned NOT NULL auto_increment,
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;</code>

2. 填充表：

<code class="language-sql">INSERT INTO `numbers`
  ( `id` )
VALUES
  ( NULL ); -- 添加足够多的空行以覆盖所需的日期范围</code>

这将生成与日期范围所需行数一样多的行。 (注意：需要根据日期范围调整插入行数，单纯的NULL插入并不能自动生成足够多的行)

3. 构建日期序列：

<code class="language-sql">SELECT DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY)
FROM `numbers` `n`
WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` -1 DAY) <= '2010-06-14';</code>

将日期“2010-06-06”和“2010-06-14”替换为您所需的 YYYY-MM-DD 格式的起始日期和结束日期。

填充缺失日期

1. 创建 LEFT JOIN：

<code class="language-sql">SELECT `x`.`timestamp` AS `timestamp`,
          COALESCE(`y`.`score`, 0) AS `cnt`
FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY), '%Y-%m-%d') AS `timestamp`
             FROM `numbers` `n`
            WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY) <= '2010-06-14') x
LEFT JOIN `y` ON STR_TO_DATE(`y`.`date`, '%Y-%m-%d') = `x`.`timestamp`;</code>

将 y 替换为您实际的数据表，并相应地调整日期格式。 (建议使用统一的YYYY-MM-DD格式，避免格式转换带来的问题)

通过执行此查询，您可以获得指定范围内的完整日期集，其中缺失的值将填充为指定的 0 值。 (注意：numbers表需要预先填充足够多的行，以确保涵盖整个日期范围。)

以上是如何填充 MySQL 日期范围中缺失的日期？的详细内容。更多信息请关注PHP中文网其他相关文章！