我有一个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 不会返回任何内容。我希望没有数据的日期的结果为零。
我读到您应该创建一个所有可能日期的连接表。这是唯一的办法吗?
你可以尝试这样的事情
Declare @INT DATETIME = null
SELECT COUNT( CASE WHEN @INT IS NOT NULL THEN @INT ELSE NULL END)
我不确定 mysql,但在 sql 服务器中它会是这样的......
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