MySQL 棘手的日期时间查询

Question

我有一个有效的日期时间查询 qreat：

$q="SELECT COUNT(blocked) AS sum_blocked, DATE(created) AS date_label ".
   "FROM sms_subscribers WHERE blocked=0 GROUP BY DATE(created)";

唯一的问题是我无法弄清楚如何/是否可以用零或空来填充零结果的日期。

IE;如果 2011-08-25 有 5 条记录，2011-08-27 有 7 条记录，我希望结果返回 5, 0, 7 或 5, null, 7

任何帮助将不胜感激！

Answer 1

您可以创建一个包含天数的附加表并连接两个表。这是一个示例，它适用于指定月份 -

CREATE TABLE days (
  number INT(11) NOT NULL
);

CREATE TABLE sms_subscribers (
  id INT(11) NOT NULL AUTO_INCREMENT,
  created DATETIME DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO days VALUES 
  (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
  (11), (12), (13), (14), (15), (16), (17), (18), (19), (20),
  (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31);

INSERT INTO sms_subscribers VALUES 
  (1, '2011-08-25 08:35:27'),
  (2, '2011-08-11 08:35:30'),
  (3, '2011-08-06 08:35:35'),
  (4, '2011-06-06 08:35:40'),
  (5, '2011-06-19 08:35:47'),
  (6, '2011-06-01 08:35:53'),
  (7, '2011-08-28 08:36:02'),
  (8, '2011-08-25 08:57:58');


SELECT d.number, COUNT(t.created) FROM days d
  LEFT JOIN (SELECT * FROM sms_subscribers
              WHERE YEAR(created) = 2011 AND MONTH(created) = 8) t -- Specify year and monts here
    ON DAYOFMONTH(t.created) = d.number
WHERE
  d.number <= DAYOFMONTH(LAST_DAY('2011-08-01')) -- Specify any day in the specified month, it will help to found out max number of days in the month
GROUP BY d.number;

+--------+------------------+
| number | COUNT(t.created) |
+--------+------------------+
|      1 |                0 |
|      2 |                0 |
|      3 |                0 |
|      4 |                0 |
|      5 |                0 |
|      6 |                1 |
|      7 |                0 |
|      8 |                0 |
|      9 |                0 |
|     10 |                0 |
|     11 |                1 |
|     12 |                0 |
|     13 |                0 |
|     14 |                0 |
|     15 |                0 |
|     16 |                0 |
|     17 |                0 |
|     18 |                0 |
|     19 |                0 |
|     20 |                0 |
|     21 |                0 |
|     22 |                0 |
|     23 |                0 |
|     24 |                0 |
|     25 |                2 |
|     26 |                0 |
|     27 |                0 |
|     28 |                1 |
|     29 |                0 |
|     30 |                0 |
|     31 |                0 |
+--------+------------------+