在MySQL中填滿日期範圍內的缺失日期
在某些情況下,您可能會遇到日期範圍內的日期缺失的表。這可能會擾亂資料分析和繪圖過程。為了彌補這一差距,MySQL 提供了一種解決方案,可以使用指定的數值來填充缺失的日期。
讓我們考慮一個記錄與特定日期關聯的分數的表格的範例。如果省略某些日期,我們的目標是使用佔位符值(例如 0)來完成它們,以獲得連續的範圍。
應對挑戰
MySQL 缺乏原生遞歸功能,但我們可以利用 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>
<code class="language-sql">INSERT INTO `numbers` ( `id` ) VALUES ( NULL ); -- 添加足够多的空行以覆盖所需的日期范围</code>
這將產生與日期範圍所需行數一樣多的行。 (注意:需要根據日期範圍調整插入行數,單純的NULL
插入並不能自動產生足夠多的行)
<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 格式的起始日期和結束日期。
填充缺失日期
<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中文網其他相關文章!