mysql - 使用用户变量显示 mysql 中日期之间的数据

Question

我想显示给定日期之间的所有数据。没有找到数据的日期我想为所有列显示 null

表架构

TimeEntry(id: integer, project_id: integer, user_id: integer, hours: float, comments: string, spent_on: date)

到目前为止我尝试了什么，

set @num = -1;
SELECT DATE_ADD(  '2009-01-01', INTERVAL @num := @num +1
DAY ) AS date_sequence, time_entries.id
FROM time_entries
WHERE user_id =55
HAVING DATE_ADD(  '2009-01-01', INTERVAL @num DAY ) <=  '2012-01-01'

输出结果如下

date_sequence id
2009-01-01  46
2009-01-02  50
2009-01-03  55
2009-01-04  01
2009-01-05  02
2009-01-06  24
2009-01-07  57
2009-01-08  59
2009-01-09  74
2009-01-10  78

预期输出是

date_sequence id
2009-01-01  NULL
2009-01-02  NULL
2009-01-03  NULL
2009-01-04  NULL
2009-01-05  NULL
2009-01-06  NULL
2009-01-07  NULL
2009-01-08  NULL
2009-01-09  74
2009-01-10  78

因为id只存在于09和10

如何将日期与其他字段进行匹配？

提示/评论？

基于 Rails 的解决方案也很有用。

编辑:

到目前为止我在 Rails 中尝试了什么

def create_dates(from_time,till_time)
  dates = []
  date_from = from_time.to_time
  while date_from <= till_time.to_time && periods.length <= 100          
      dates << "#{date_from.to_date}"
      date_from = date_from + 1.day
  end
dates

结束

它返回给定时间段之间的日期。但我无法将我的数据与这些日期相匹配

Answer 1

试试这个:

set @num = -1;
select t1.date_sequence, count(t2.id) from
 (SELECT DATE_ADD(  '2009-01-01', INTERVAL @num := @num +1 DAY ) AS date_sequence
  FROM time_entries HAVING DATE_ADD(  '2009-01-01', INTERVAL @num + 1 DAY ) <=  '2012-01-01') as t1
left outer join time_entries as t2 on t1.date_sequence = t2.spent_on
group by t1.date_sequence
order by t1.date_sequence;