mysql - 按日期分组行，使用日历表

Question

下表:

members
-----------------------------------------------
id      name        created_at
-----------------------------------------------
1       John        2014-05-08 10:18:12
2       Josh        2014-05-07 10:10:36
3       Mark        2014-04-30 12:10:04

日历:

-----------------------------------------------
calendar_date
-----------------------------------------------
1970-01-01
1970-01-02
...
2014-05-07
2014-05-08
2014-05-09
...

这是我的查询:

SELECT
  calendar.calendar_date,
  COUNT(members.id)
FROM
  calendar
LEFT JOIN
  members
    ON  DATE(members.created_at) >= calendar.calendar_date
    AND DATE(members.created_at) <  calendar.calendar_date + 1
WHERE
      calendar.calendar_date >= '2014-05-01'
  AND calendar.calendar_date <  '2014-05-20'
GROUP BY
  calendar.calendar_date

这是错误的。日期 07-08 应该只有计数 1。其余应该为 0。

我的查询有什么问题吗？

Answer 1

而不是

LEFT JOIN
  members
    ON  DATE(members.created_at) >= calendar.calendar_date
    AND DATE(members.created_at) <  calendar.calendar_date + 1

写

LEFT JOIN
  members
    ON  DATE(members.created_at) = DATE(calendar.calendar_date)

因为您的 calendar.calendar_date 字段是日期时间字段，也应该只截断为日期值。

或者为了绝对确定，你也可以这样做:

LEFT JOIN
  members
    ON DATE_FORMAT(members.created_at, '%Y%m%d') =
       DATE_FORMAT(calendar.calendar_date, '%Y%m%d')