MySQL 返回当月所有日期的计数，包括结果为零的日期

Question

我有一个如下所示的 MySQL 表:

+--------+------------+------------------+
|   id   | account_id |     posted_at    |
+--------+------------+------------------+
|   1    |       1    | 2013-10-05 23:09 |
|   2    |       1    | 2013-10-07 14:24 |
|   3    |       1    | 2013-10-07 01:17 |
|   4    |       1    | 2013-10-09 06:58 |
+--------+------------+------------------+

对于特定的 account_id(在本例中为 1)，我想返回此值(对于当月的日期):

+--------+------------+
|  count |    date    |
+--------+------------+
|    0   | 2013-10-01 |
|    0   | 2013-10-02 |
|    0   | 2013-10-03 |
|    0   | 2013-10-04 |
|    1   | 2013-10-05 |
|    0   | 2013-10-06 |
|    2   | 2013-10-07 |
|    0   | 2013-10-08 |
|    1   | 2013-10-09 |
+--------+------------+

我有一个 SQL 查询，它返回本月内每个日期的 COUNTS。

SELECT 
    DATE(posted_at) AS formatted_date, 
    COUNT(id) AS count 
FROM entries 
WHERE account_id = 1 
    AND MONTH(DATE(posted_at)) = MONTH(NOW()) 
GROUP BY formatted_date 
ORDER BY formatted_date ASC

它只是返回这个:

+--------+------------+
|  count |    date    |
+--------+------------+
|    1   | 2013-10-05 |
|    2   | 2013-10-07 |
|    1   | 2013-10-09 |
+--------+------------+

当然，对于没有数据的日期，COUNT 不会返回任何内容。我希望结果对于没有数据的日期为零。

我读到您应该创建所有可能日期的联接表。这是唯一的方法吗？

Answer 1

你可以尝试这样的事情

Declare @INT DATETIME = null
SELECT COUNT( CASE WHEN @INT IS NOT NULL THEN @INT ELSE NULL END)

我不确定 mysql，但在 sql server 中它会是这样的......

SELECT 
    DATE(posted_at) AS formatted_date, 
    COUNT( CASE WHEN IS NOT NULL THEN posted_at ELSE NULL END ) AS [count] 
FROM entries 
WHERE account_id = 1 
    AND MONTH(DATE(posted_at)) = MONTH(NOW()) 
GROUP BY formatted_date 
ORDER BY formatted_date ASC