mysql date list with count even if no data on spec

问题:

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

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?

回答1:

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

produces:

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

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

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

回答2:

in MySQL, I would suggest creating a Calendar table with the dates listed. Then you will join on that table. Similar to this:

CREATE TABLE Table1(`Postid` int, `date` datetime, `text` varchar(3));

INSERT INTO Table1(`Postid`, `date`, `text`)
VALUES
    (1, '2011-12-31 17:00:00', 'bla'),
    (2, '2011-12-31 17:00:00', 'bla'),
    (3, '2012-01-01 17:00:00', 'bla'),
    (4, '2012-01-01 17:00:00', 'bla'),
    (5, '2012-01-03 17:00:00', 'bla'),
    (6, '2012-01-03 17:00:00', 'bla'),
    (7, '2012-01-04 17:00:00', 'bla');

CREATE TABLE Table2(`date` datetime);

INSERT INTO Table2(`date`)
VALUES('2011-12-31 17:00:00'),
    ('2012-01-01 17:00:00'),
    ('2012-01-02 17:00:00'),
    ('2012-01-03 17:00:00'),
    ('2012-01-04 17:00:00'),
    ('2012-01-05 17:00:00'),
    ('2012-01-06 17:00:00'),
    ('2012-01-07 17:00:00'),
    ('2012-01-08 17:00:00');

select t2.date, count(postid)
from table2 t2
left join table1 t1
  on t2.date = t1.date
group by t2.date

See SQL Fiddle with Demo