mysql - 查找丢失的日期并将它们添加到结果中 - MySql

Question

我有一个如下所示的查询:

  SELECT count(*)
        ,date_format(created, '%d/%m/%y ') as datecreated
    FROM mimesi_indexer.meta_served_clips
GROUP BY DATE(created)
ORDER BY created ASC

问题是数据库中缺少一些日期。我需要将这些日期显示在显示日期计数为 0 和日期本身的结果中。请问我该怎么做？谢谢

编辑:

count(*) 在左栏
创建是正确的

a) 数据:

2610, 11/04/17
1332, 12/04/17
2082, 26/04/17
3584, 27/04/17

b) 预期结果:

2610   11/04/17 
1332   12/04/17 
0      13/04/17 
0      14/04/17 
0      15/04/17 
0      16/04/17 
0      17/04/17 
0      18/04/17 
0      19/04/17 
0      20/04/17 
0      21/04/17 
0      22/04/17 
0      23/04/17 
0      24/04/17 
0      25/04/17 
2082   26/04/17 
3584   27/04/17 

c) 实际结果:

2610    11/04/17
1332    12/04/17
2082    26/04/17
3584    27/04/17

Answer 1

这可能不是一个完美的解决方案，但这可能会给您以下结果:内部 SELECT 中 UNION ALL 之后的第二个查询应返回表的 MIN(created) 和 MAX(created) 之间的所有日期计数器值为 0。

    SELECT SUM(a.ctr)
      ,a.datecreated
  FROM 
  (

    SELECT COUNT(*) as ctr, date_format(created, '%d/%m/%y ') as datecreated 
    FROM mimesi_indexer.meta_served_clips
    GROUP BY DATE(created)
UNION ALL 
    select 0 as ctr, date_format(selected_date, '%d/%m/%y ') as datecreated 
       from 
     (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
    where selected_date 
    between (SELECT MIN(created) FROM mimesi_indexer.meta_served_clips)
        and (SELECT MAX(created) FROM mimesi_indexer.meta_served_clips)
   ) a
   group by a.datecreated
   order by month(a.datecreated), date(a.datecreated)