Bagaimana untuk Memasukkan Hari Pertumbuhan Sifar Apabila Memilih Julat Tarikh dalam MySQL?

MySQL: Pilih Semua Tarikh Dalam Julat Termasuk Sifar Hari Pertumbuhan

Apabila menggambarkan pertumbuhan pangkalan pengguna, adalah penting untuk memasukkan hari tanpa pertumbuhan . Untuk mencapai matlamat ini dalam MySQL, kami menggunakan teknik yang dikenali sebagai produk Cartesian.

Pertimbangkan pertanyaan berikut:

SELECT DATE(datecreated), count(*) AS number FROM users
WHERE DATE(datecreated) > '2009-06-21' AND DATE(datecreated) <p>Pertanyaan ini mengembalikan hasil selama beberapa hari dengan sekurang-kurangnya seorang pengguna. Untuk memasukkan tarikh dengan pertumbuhan sifar, kami mencipta jadual tarikh yang dijana daripada produk Cartes:</p><pre class="brush:php;toolbar:false">select date_add('2003-01-01 00:00:00.000', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date
from
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n1,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n2,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n3,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n4,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n5

Ini menghasilkan jadual dengan tarikh berjujukan antara '2003-01-01' hingga 'now() ', membolehkan kami melakukan gabungan kiri dengan jadual pengguna kami:

select  * from (
select date_add('2003-01-01 00:00:00.000', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date from
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n1,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n2,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n3,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n4,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n5
) a
where date >'2011-01-02 00:00:00.000' and date <p>Pertanyaan ini memastikan bahawa hari pertumbuhan sifar diwakili dalam keputusan akhir.</p>

Atas ialah kandungan terperinci Bagaimana untuk Memasukkan Hari Pertumbuhan Sifar Apabila Memilih Julat Tarikh dalam MySQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!