MySQL - 填充缺失的日期
我有这个查询，我想用一些值填充缺失的日期(例如零...)
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;
相关文章