mysql - 我正在处理查询，当有事件时我会计算日期我也想在日期范围内没有事件时得到计数 0

Question

日期范围是最近 7 天。目前我正在从这个查询中获取这些数据

counts dates
1      2018-12-25
1      2018-12-26
3      2018-12-30

查询是

select COALESCE(Count(campaign_id), 0) as counts,date(start_date) as dates from campaigns where segment_id=30 
and date(start_date) BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND CURDATE()
group by date(start_date)

但我想要预期的输出是

counts dates
0      2018-12-24
1      2018-12-25
1      2018-12-26
0      2018-12-27
0      2018-12-28
0      2018-12-29
3      2018-12-30

Answer 1

你可以使用information_schema的 View 生成7行，例如information_schema.tables

select (select count(*) 
          from campaigns
         where start_date = e.dates
        ) count,
        e.dates
  from
  (
    select * 
      from campaigns c   
      right join
        (
        SELECT  @cr := @cr + 1 as rn,       
                date_sub(curdate(), interval 7 - @cr day) as dates
          from information_schema.tables c1 
         cross join (SELECT @cr := 0, @segID := 30) r
        where @cr<7
        ) d on c.campaign_id = d.rn
    where coalesce(c.segment_id,@segID) = @segID
   ) e;

count   dates
  0   24.12.2018 
  1   25.12.2018 
  1   26.12.2018 
  0   27.12.2018 
  0   28.12.2018 
  0   29.12.2018 
  3   30.12.2018  

Rextester Demo