查找缺少的日期并将其添加到结果中-MySql [英] Find missing date and add them into result - MySql

问题描述

我有一个如下查询:

  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和日期本身.我该怎么办? 谢谢

The problem is that some dates are missing in the database. I need to have those dates into the results showing the date's count as 0 and the date itself. How can I do it please? Thanks

计数(*)在左列
创建是正确的

count(*) is in the left column
created is the right one

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

推荐答案

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

it might not be a perfect solution, but this might give you the result: the second query after UNION ALL in the inner SELECT should return all dates between the MIN(created) and the MAX(created) of your table with the counter value 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)

这篇关于查找缺少的日期并将其添加到结果中-MySql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！