添加空白以填补空白的 MySQL 查询 [英] MySQL Query that adds blanks to fill the gaps

问题描述

我想要一个 MySQL 查询，在给定日期的情况下，它将返回最近 7 天的结果，但在 MySQL 表中可能有空格.

I would like to have a MySQL query that given a specified date it will return the last 7 days of results, but in the MySQL table there could be blanks.

所以表格看起来像这样

tblMyData

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

但是我的查询需要填写空白，所以我的结果看起来像这样

However my query would need to fill in the blanks so my results would look like this

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

有什么想法可以做到这一点吗?

Any ideas how I can do this?

推荐答案

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

One solution is to generate the dates with a subquery, and then join this subquery with your table.

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

If you only need the last 7 days then you can try with this:

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，那么您的查询可以是这样的:

if instead of the current_date, you want the last 7 days in the table, then your query can be like this:

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

请在此处查看小提琴.

这篇关于添加空白以填补空白的 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！