如何填入 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中文網其他相關文章！