mysql - 我的 sql 查询最近 7 天的数据？如果数据不存在，那么甚至获取日期并显示为 0 或 null

Question

我已经在 sql Fidldle 中制作了一个演示实体。我的表有以下列

displayId | displaytime | campaignId

现在我想要的是为特定的 campaignId 选择最近 7 天的条目(我的意思是 campaignId="") 同一日期应该有多个具有相同事件 ID 的条目。所以我想显示总事件 ID 及其日期的总和

所以如果日期 2013-3-8 和其他日期有 7 条记录，那么它会显示像这样的记录

2013-03-02 | 1
2013-03-03 | 1
2013-03-04 | 0
2013-03-05 | 0
2013-03-06 | 0
2013-03-07 | 2
2013-03-08 | 7

在下面的查询中，它只显示日期及其计数超过 0 的...例如我试过的是如下..但它只给了我一条在数据库中有条目的记录......

 2013-03-08 | 7


SELECT date(a.displayTime) AS `DisplayTime`,
       ifnull(l.TCount,0) AS TCount
FROM a_ad_display AS a
INNER JOIN
  (SELECT count(campaignId) AS TCount,
          displayTime
   FROM a_ad_display
   WHERE CONVERT_TZ(displaytime,'+00:00','-11:00') >= DATE_SUB(CONVERT_TZ(CURDATE(),'+00:00','-11:00') ,INTERVAL 1 DAY)
     AND CONVERT_TZ(displaytime,'+00:00','-11:00') <= CONVERT_TZ(CURDATE(),'+00:00','-11:00')
     AND campaignId = 20747
   GROUP BY DATE(displayTime)) AS l ON date(a.displayTime) = date(l.displayTime)
GROUP BY DATE(a.displayTime)
ORDER BY a.displaytime DESC LIMIT 7

我已经在我的查询中实现了时区，所以如果你能帮助我完成简单的查询，那么它没问题......不要包含 Convert_Tz 行。

这是 http://sqlfiddle.com/#!2/96600c/1我的虚拟实体的链接

Answer 1

如果您使用日期表，您的查询可以像这样简单:

select dates.fulldate, count(a_ad_display.id)
from dates
left outer join a_ad_display ON dates.fulldate = a_ad_display.displaytime
    [AND <other conditions here>]
where dates.fulldate BETWEEN date_add(curdate(), interval -6 day) AND curdate()
group by dates.fulldate

http://sqlfiddle.com/#!2/51e17/3

---

在不使用日期表的情况下，这是一种方法。它很难看，而且性能可能很糟糕:

select displaytime, sum(c)
from
(
  select displaytime, count(a_ad_display.id) AS c
  from a_ad_display
  where displaytime BETWEEN date_add(curdate(), interval -6 day) AND curdate()
  group by displaytime
  union all
  select curdate(), 0
  union all
  select date_add(curdate(), interval -1 day), 0
  union all
  select date_add(curdate(), interval -2 day), 0
  union all
  select date_add(curdate(), interval -3 day), 0
  union all
  select date_add(curdate(), interval -4 day), 0
  union all
  select date_add(curdate(), interval -5 day), 0
  union all
  select date_add(curdate(), interval -6 day), 0
) x
group by displaytime
order by displaytime

http://sqlfiddle.com/#!2/96600c/16