即使特定日期没有数据，mysql日期列表也包含计数[重复]

可能的重复：
MySQL如何填充范围内缺失的日期？ https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range

我正在尝试从 mysql 数据制作图表，

Postid | date       | text
1      | 2012-01-01 | bla
2      | 2012-01-01 | bla
3      | 2012-01-02 | bla
4      | 2012-01-02 | bla
5      | 2012-01-04 | bla
6      | 2012-01-04 | bla
7      | 2012-01-05 | bla

现在，我想获取每天的帖子数量，包括日期为零的帖子。例如，我希望能够像这样获得第一周：

date       | count(Postid)
2012-01-01 | 2
2012-01-02 | 2
2012-01-03 | 0
2012-01-04 | 2
2012-01-05 | 1
2012-01-06 | 0
2012-01-07 | 0

我正在寻找一个通用的解决方案，我不必指定每个日期。有什么建议么？

假设你的postids 在表中是连续的，那么这个查询：

SELECT
  DATE_FORMAT(b.date, '%Y-%m-%d') date,
  COUNT(c.postid)
FROM
(
  SELECT
    (SELECT MAX(date) FROM ex) + INTERVAL 3 DAY - INTERVAL a.postid DAY AS date
  FROM
    ex a
) b
LEFT JOIN
  ex c ON c.date = b.date
GROUP BY
  b.date
ORDER BY
  b.date

产生：

date    COUNT(c.postid)
2012-01-01  2
2012-01-02  2
2012-01-03  0
2012-01-04  2
2012-01-05  1
2012-01-06  0
2012-01-07  0

See http://sqlfiddle.com/#!2/2cf8d/2 http://sqlfiddle.com/#!2/2cf8d/2

If your postids 不连续，那么您可以使用ids连续 id 表：

SELECT
  DATE_FORMAT(b.date, '%Y-%m-%d') date,
  COUNT(c.postid)
FROM
(
  SELECT
    (SELECT MAX(date) FROM ex) + INTERVAL 3 DAY - INTERVAL a.id DAY AS date
  FROM
    ids a
) b
LEFT JOIN
  ex c ON c.date = b.date
GROUP BY
  b.date
ORDER BY
  b.date DESC
LIMIT 7

See http://sqlfiddle.com/#!2/13035/1 http://sqlfiddle.com/#!2/13035/1