首頁 > 資料庫 > mysql教程 > 如何填入 MySQL 日期範圍中缺少的日期?

如何填入 MySQL 日期範圍中缺少的日期?

Mary-Kate Olsen
發布: 2025-01-23 17:02:10
原創
369 人瀏覽過

How to Fill Missing Dates in a MySQL Date Range?

在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>
登入後複製
  1. 填充表:
<code class="language-sql">INSERT INTO `numbers`
  ( `id` )
VALUES
  ( NULL ); -- 添加足够多的空行以覆盖所需的日期范围</code>
登入後複製

這將產生與日期範圍所需行數一樣多的行。 (注意:需要根據日期範圍調整插入行數,單純的NULL插入並不能自動產生足夠多的行)

  1. 建構日期序列:
<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中文網其他相關文章!

來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
作者最新文章
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板