mysql - 创建填充有日期并左连接到 `created_at` 列的临时表 - MySQL

Question

原始查询:

// Doesn't return dates with zero value

SELECT UNIX_TIMESTAMP( created_at ) AS DATE, COUNT( tweet_id ) AS count
FROM  `tweets` 
WHERE DATE( created_at ) >  '2012-11-01'
AND DATE( created_at ) <= DATE( NOW( ) ) 
GROUP BY DATE

修改后的查询:

// Attempting to return all dates with a value of zero if doesn't exist in `created_at` column

CREATE TEMPORARY TABLE DateSummary1 ( date timestamp ) SELECT DISTINCT(DATE(created_at)) as date FROM tweets;
CREATE TEMPORARY TABLE DateSummary2 ( date timestamp, number int ) SELECT DATE(created_at) as date, count(*) AS number FROM tweets WHERE DATE(created_at) > '2012-11-01' AND DATE(created_at) <= DATE(NOW()) GROUP BY DATE(created_at) ORDER BY created_at ASC;
SELECT ds1.date,ds2.number FROM DateSummary1 ds1 LEFT JOIN DateSummary2 ds2 on ds1.date=ds2.date;

不幸的是，后一个结果没有像我预期的那样为我提供零值的日期。我在忽略什么？我确信这是一个逻辑错误，但我不确定我的逻辑错在哪里。我已经阅读了 SO、Google 等上的大量帖子，但不知道如何克服这最后的障碍。

使用 jcho360 建议返回的时间戳的示例:

1387286811
1387286812
1387286813
1387286815
1387286820

Answer 1

试试这个

SELECT UNIX_TIMESTAMP( created_at ) AS DATE, COUNT( tweet_id ) AS count
FROM  `tweets` 
WHERE (DATE( created_at ) >  '2012-11-01'
AND DATE( created_at ) <= DATE( NOW( ) ) )
or DATE( created_at ) = date ('000-00-00') //I added this Line
GROUP BY DATE