Mysql select date day after day

Question

I have a table shown below:

                      login
          date                     user    
       2016-11-23                   1
       2016-11-23                   2
       2016-11-23                   3
       2016-11-25                   2
       2016-11-25                   5
       2016-11-27                   1

from the table above, what I want to get looks like this:

      date                   count(*)
   2016-11-21                   0
   2016-11-22                   0    
   2016-11-23                   3
   2016-11-24                   0
   2016-11-25                   2
   2016-11-26                   0
   2016-11-27                   1

But since there are only dates 2016-11-23and 2016-11-25and 2016-11-27when I request the following:

select date, count(*)
from login
where date between (current_date()-interval 7 day) and current_date()
group by date
order by date asc

He cannot get the result, like what I really want to get. Is this possible from my table login?

Answer 1

One way is to generate all days before JOIN

select GenDate, count(Date)
from login
right join
(select a.GenDate 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as GenDate
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.GenDate between (current_date()-interval 7 day) and current_date())x
ON x.GenDate=login.Date
group by GenDate
order by GenDate asc

Answer 2

Use a view with the desired dates:

SELECT t.date, count(s.date)
FROM (SELECT '2016-11-21' as `date` UNION ALL
      SELECT '2016-11-22' as `date` UNION ALL
       ...) t
LEFT JOIN login s
 ON(t.date = s.date)
WHERE
    t.date between (current_date()-interval 7 day) and current_date()
GROUP BY t.date
ORDER BY t.date

Answer 3

. .

• PHP .

• AS sagi suggested creating a separate table containing all the dates in the range of days your application was running, after which you can join this table with your request. One problem is that from time to time you have to add more days to this table if you suddenly had no days in the future or in the past.