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