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

如果您想要表中显示过去 7 天而不是 current_date，那么您的查询可以如下所示:

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

请看 fiddle here .