Bagaimana untuk Mengisi Tarikh Hilang dalam Julat Tarikh MySQL?

Isi tarikh yang tiada dalam julat tarikh dalam MySQL

Dalam sesetengah kes, anda mungkin menghadapi jadual dengan tarikh yang tiada dalam julat tarikh. Ini boleh mengganggu analisis data dan proses plot. Untuk merapatkan jurang ini, MySQL menyediakan penyelesaian untuk mengisi tarikh yang hilang dengan nilai angka yang ditentukan.

Mari kita pertimbangkan contoh jadual yang merekodkan markah yang dikaitkan dengan tarikh tertentu. Jika beberapa tarikh ditinggalkan, matlamat kami adalah untuk melengkapkannya dengan nilai pemegang tempat (cth. 0) untuk mendapatkan julat bersebelahan.

Memenuhi cabaran

MySQL tidak mempunyai fungsi rekursif asli, tetapi kita boleh menggunakan helah jadual NUMBERS untuk menjana jujukan tarikh berdasarkan lajur peningkatan automatik.

Jana jujukan tarikh

1. Buat jadual 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. Isi jadual:

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

Ini akan menjana seberapa banyak baris yang diperlukan oleh julat tarikh. (Nota: Bilangan baris yang disisipkan perlu dilaraskan mengikut julat tarikh. Hanya memasukkan NULL tidak boleh menjana baris yang mencukupi secara automatik)

3. Bina jujukan tarikh:

<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>

Gantikan tarikh "2010-06-06" dan "2010-06-14" dengan tarikh mula dan tamat yang anda inginkan dalam format YYYY-MM-DD.

Isi tarikh yang tiada

1. Buat 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>

Ganti y dengan jadual data sebenar anda dan laraskan format tarikh dengan sewajarnya. (Adalah disyorkan untuk menggunakan format YYYY-MM-DD bersatu untuk mengelakkan masalah yang disebabkan oleh penukaran format)

Dengan melaksanakan pertanyaan ini, anda mendapat set tarikh lengkap dalam julat yang ditentukan, di mana nilai yang tiada diisi dengan nilai 0 yang ditentukan. (Nota: numbersJadual perlu dipraisi dengan baris yang mencukupi untuk memastikan keseluruhan julat tarikh dilindungi.)

Atas ialah kandungan terperinci Bagaimana untuk Mengisi Tarikh Hilang dalam Julat Tarikh MySQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!