問題描述

我有這個(gè)查詢，我想用一些值填充缺失的日期(例如零...)

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

結(jié)果如下:

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

我需要這些數(shù)據(jù)用于圖表預(yù)覽.

I need this data for chart preview.

推薦答案

一般情況下，你可以在 MySQL 中使用以下方法生成一系列 N 個(gè)整數(shù):

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

請(qǐng)注意，您加入的表 (someTable) 必須至少有 N 行. 上面的 -1 是使其基數(shù)為零...刪除它，您將得到 1,2,3 對(duì)于 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.

您可以將這些整數(shù)輸入 DATE_ADD 函數(shù)以將其轉(zhuǎn)換為一系列日期.為了便于理解，讓我們?yōu)槿掌谑褂靡恍┯脩糇兞?

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

這將返回這些天以及它們之間的每一天的行.現(xiàn)在只是加入你的表的問題......我沒有嘗試過，因?yàn)槲覜]有你的數(shù)據(jù)庫(kù)結(jié)構(gòu)，但像下面這樣的東西應(yīng)該可以工作:

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;

這篇關(guān)于MySQL - 填充缺失的日期的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！