mysql日期列表与计数，即使在特定日期没有数据 [英] mysql date list with count even if no data on specific date

问题描述

可能重复:
MySQL如何填充范围内的缺失日期?

Possible Duplicate:
MySQL how to fill missing dates in range?

我正在尝试从mysqldata制作图形，

I'm trying to make a graph from mysqldata,

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

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

Now, I'd like to get the number of posts on every day, INCLUDING dates with zero. For example, i'd like to be able to get the first week like this:

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

我正在寻找一种通用的解决方案，无需在此指定每个日期.有什么建议吗?

I'm looking for a generic solution, where i don't have to specify every date. Any suggestions?

推荐答案

假设您的postid在表中是连续的，则此查询:

Assuming your postids are contiguous in your table, then this query:

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

请参见 http://sqlfiddle.com/#!2/2cf8d/2

如果您的postid不连续，则可以使用连续ID的ids表:

If your postids are not contiguous, then you can use an ids table of contiguous ids:

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

请参见 http://sqlfiddle.com/#!2/13035/1

这篇关于mysql日期列表与计数，即使在特定日期没有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！