MySQL - fill missing dates [duplicate]

问题:

This question already has an answer here:

• MySQL how to fill missing dates in range? 3 answers

I have this query and I want to fill missing dates with some values (zero for example...)

SELECT date, SUM(val1) as sum_val  
FROM table1  
WHERE date BETWEEN '2016-03-04' AND '2016-03-10'  
GROUP BY date

Here is result:

|--------------------------  
|date, sum_val  
|--------------------------  
|2016-03-04, 21568  
|--------------------------  
|2016-03-05, 14789  
|--------------------------  
|2016-03-08, 20568  
|--------------------------  
|2016-03-10, 5841  
|--------------------------

How can I populate missing dates with zero values? Does anyone has an idea?

I need this data for chart preview.

回答1:

In general, you can generate a series of N integers in MySQL using:

    select (@i:=@i+1)-1 as `myval` from someTable,(SELECT @i:=0) gen_sub limit N

Note that the table that you join on (someTable) must have at least N rows. The -1 above is to make it base-zero... remove it and you'll get 1,2,3 for N=3.

You can feed those integers into the DATE_ADD function to turn it into a series of dates. To make it easier to follow, let's use some user variables for the dates.

SET @date_min = '2016-03-04';
SET @date_max = '2016-03-10';

select DATE_ADD(@date_min, INTERVAL (@i:=@i+1)-1 DAY) as `date`
from information_schema.columns,(SELECT @i:=0) gen_sub
where DATE_ADD(@date_min,INTERVAL @i DAY) BETWEEN @date_min AND @date_max

That will return rows for those days and every day between them. Now it's just a matter of joining against your table... I haven't tried it since I don't have your db structure, but something like the following should work:

SET @date_min = '2016-03-04';
SET @date_max = '2016-03-10';

SELECT
   date_generator.date,
   ifnull(SUM(val1),0) as sum_val
from (
   select DATE_ADD(@date_min, INTERVAL (@i:=@i+1)-1 DAY) as `date`
   from information_schema.columns,(SELECT @i:=0) gen_sub 
   where DATE_ADD(@date_min,INTERVAL @i DAY) BETWEEN @date_min AND @date_max
) date_generator
left join table1 on table1.date = date_generator.date
GROUP BY date;

回答2:

You can try this PHP loop:

$date1 = Your_first_date_value;
$date2 = Your_limit_date;
for ($d1 = $date1; d1<=$date2; strtotime("+1 day", strtotime($d1))) {
    //Fill in values
}

strtotime("+1 day", strtotime($date1)) will increment by 1 day the current value of $date1 until it is equal to $date2.

When a date does not have a value it will be zero, and if the date exist, it will get the sum according to MySQL result.

回答3:

Use below code its similar to your query and surely work and helful to you.

$query = "SELECT date, SUM(val1) as sum_val  
FROM table1  
WHERE date BETWEEN '2016-03-04' AND '2016-03-10'  
GROUP BY date";
$result = mysql_db_query($db,$query);
$dateArray = Array();
foreach($result as $row)
{
    $dateArray[] = strtotime($row['date']);
}


$from = "1-4-2016";
$to = "30-4-2016";
$datediff = strtotime($from) - strtotime($to);
$days = floor($datediff/(60*60*24));
for ($d1 = 1; $d1<$days; $d1++) {
    $cdate = strtotime("+".$d1." day", strtotime($from));
    if(!in_array($cdate, $dateArray))
    {
        // Insert record with zero
    }
}