MySQL按天选择日期

Question

我有下面显示的表格：

                      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

我想要从上表中获得以下内容：

      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

然而，由于只有日期 2016-11-23、2016-11-25 和 2016-11-27，所以当我进行如下查询时：

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

我无法得到我真正想要的结果。这个结果可能来自我的登录表吗？

- user7159879

Answer 1

一种方法是在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

- Mihai

---

实际上，在这里使用COUNT(*)并不好，它会对没有匹配项的日期返回1。 - sagi

Answer 2

这是编程中非常常见的问题，有几种解决方案。

1. 使用PHP遍历结果，在结果数组中填充缺失的日期。

2. 正如sagi所建议的那样，创建一个单独的表格，其中包含应用程序使用的所有日期范围内的日期，然后可以将该表格与查询进行JOIN操作。其中一个问题是，不时需要向此表格添加更多日期，如果未来或过去出现缺失日期。

- Sych

Answer 3

使用所需日期的派生表：

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

- sagi