php - 使当天的访问者计数等于0，这在MySQL数据库中不存在

我有桌子

___________________________________________
id |   user  | Visitor | timestamp
___________________________________________
13 |username |abc     | 2014-01-15 15:01:44
14 |username |abc     | 2014-01-15 15:01:44
15 |username |abc     | 2014-01-18 15:01:44
16 |username |abc     | 2014-01-18 15:01:44
___________________________________________

从今天起过去7天，我使用了QUERY来计数USER abc的访客数量。

SELECT DATE(`timestamp`) as `date`, COUNT(*) as `count`
FROM `table` WHERE (`timestamp` >= (NOW() - INTERVAL 7 DAY)) AND (`user` = 'username')
GROUP BY `date`;

它得到以下输出：

______________________________
date              |  count
______________________________
2014-01-15        |     2
2014-01-18        |     2

但是我需要：

______________________________
date              |  count
______________________________
2014-01-15        |     2
2014-01-16        |     0        // Make 0 for the day which is not present
2014-01-17        |     0        // Make 0 for the day which is not present
2014-01-18        |     2

将对此查询什么？

最佳答案

使用子查询创建一个表，其中包含过去一周的所有日期，然后将其与您的表连接：

SELECT `date`, IFNULL(COUNT(*), 0) as `count`
FROM (SELECT DATE(NOW()) AS `date`
      UNION
      SELECT DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))
      UNION
      SELECT DATE(DATE_SUB(NOW(), INTERVAL 2 DAY))
      UNION
      SELECT DATE(DATE_SUB(NOW(), INTERVAL 3 DAY))
      UNION
      SELECT DATE(DATE_SUB(NOW(), INTERVAL 4 DAY))
      UNION
      SELECT DATE(DATE_SUB(NOW(), INTERVAL 5 DAY))
      UNION
      SELECT DATE(DATE_SUB(NOW(), INTERVAL 6 DAY))
      UNION
      SELECT DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))) AS days
LEFT JOIN `table` ON DATE(`timestamp`) = `date`
WHERE (`timestamp` >= (NOW() - INTERVAL 7 DAY)) AND (`user` = 'username')
GROUP BY `date`;