MySQL查询，添加空白以填补空白

Question

我希望有一个MySQL查询，给定指定日期，它将返回最后7天的结果，但在MySQL表中可能有空格。

所以这个表可能看起来像这个

tblMyData

TestDate    |   val1   |   val2 
2014-07-10  |   20     |   30 
2014-07-09  |   10     |   10
2014-07-07  |   11     |   22
2014-07-04  |    9     |   45

然而，我的查询需要填空，所以我的结果看起来像这个

TestDate    |   val1   |   val2 
2014-07-10  |   20     |   30 
2014-07-09  |   10     |   10
2014-07-08  |    0     |    0     <<-- Added by the query
2014-07-07  |   11     |   22
2014-07-06  |    0     |    0     <<-- Added by the query
2014-07-05  |    0     |    0     <<-- Added by the query
2014-07-04  |    9     |   45

你知道我该怎么做吗？

Answer 1

一种解决方案是使用子查询生成日期，然后将此子查询与表连接。

如果你只需要最后7天，那么你可以试试这个：

select d.testdate, coalesce(t.val1,0), coalesce(t.val2,0)
from
  (select current_date as testdate
   union all select current_date - interval 1 day
   union all select current_date - interval 2 day
   union all select current_date - interval 3 day
   union all select current_date - interval 4 day
   union all select current_date - interval 5 day
   union all select current_date - interval 6 day) d
  left join tblMyData t
  on d.testdate = t.testdate

如果您想要的不是current_date，而是表中的最后7天，那么您的查询可以是这样的：

select m.m - interval d day, coalesce(t.val1,0), coalesce(t.val2,0)
from
  (select max(testdate) as m from tblMyData) m
  cross join
  (select 0 as d
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6) d
  left join tblMyData t
  on m.m - interval d day = t.testdate

请看这儿的小提琴。

Answer 2

如果你可以使用SEQUENCE ENGINE，你可以这样做。这个样品是最后100天的，但你可以更改它。

INSERT IGNORE INTO tblMyData
SELECT DATE(now() - INTERVAL seq DAY),0,0 FROM seq_0_to_100;