php - Mysql如果不存在则返回0

Question

我有一个像这样的 mysql 表:

date           data
01-01-2005       10 |
02-01-2005       11|
03-01-2005       13|
07-01-2005       20|

我怎样才能像下面这样列出

date           data:
01-01-2005       10|
02-01-2005       11|
03-01-2005       13|
04-01-2005       0|
05-01-2005       0|
06-01-2005       0|
07-01-2005       20|

编辑:我使用这样的日历表:

date:
01-01-2005 for all 365 days.

这是我最好的意见，但它不起作用:

SELECT c.steps, IFNULL(c.steps, 0) value, r.calendar_date
  FROM log_activities c
  LEFT JOIN calendar r
    ON (r.calendar_date = c.date_logged)
 WHERE email = 'a@mail.com'
 ORDER BY c.date_logged DESC

Answer 1

尝试:

SELECT c.steps, IFNULL(c.steps, 0) value, r.calendar_date
  FROM log_activities c
 right JOIN calendar r
    ON r.calendar_date = c.date_logged
 WHERE email = 'a@mail.com'
   and substr(r.calendar_date, 4, 2) = '01'
   and r.calendar_date between
       (select min(date_logged) from log_activities) and
       (select max(date_logged) from log_activities)
 ORDER BY c.date_logged DESC