MySQL-填写缺失的日期[重复]

一尘不染

MySQL-填写缺失的日期[重复]

mysql

这个问题已经在这里有了答案 ：

2年前关闭。

我有这个查询，我想用一些值（例如零…）填充缺失的日期。

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

如何用零值填充缺失的日期？有人有主意吗？

我需要这些数据来进行图表预览。

阅读 333

2020-05-17

共1个答案

一尘不染

通常，您可以使用以下方法在MySQL中生成一系列N个整数：

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

请注意，您联接的表（someTable）必须至少具有N行。 上面的-1是使它的基数为零…删除它，对于N = 3，您将得到1,2,3。

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

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

这将返回这些日期以及它们之间每天的行。现在只需要对您的表进行连接即可…因为我没有您的数据库结构，所以我没有尝试过，但是类似以下的方法应该可以工作：

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;

2020-05-17