MySQL-填写缺失的日期 [英] MySQL - fill missing dates

问题描述

我有这个查询，我想用一些值(例如零...)填充缺少的日期

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

这是结果:

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

我需要此数据用于<​​strong>图表预览.

I need this data for chart preview.

推荐答案

通常，您可以使用以下命令在MySQL中生成一系列N个整数:

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

请注意，您联接的表(someTable)必须至少具有N行.上面的-1是使它的基数为零...将其删除，您将得到1 ，2,3代表N = 3.

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.

您可以将这些整数输入到DATE_ADD函数中，以将其转换为一系列日期.为了更容易理解，我们在日期中使用一些用户变量.

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;

这篇关于MySQL-填写缺失的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！