sql - 每个日期的项目数，日期之间没有间隔

Question

我有一个网站的帖子表，其中每个条目都有其创建日期。

我想返回一个表，如:

Date         Posts
16-11-2009   5
17-11-2009   0
18-11-2009   4
etc

但我正在努力寻找正确的 SQL。我可以轻松获得按日期分组的帖子数，但是当没有帖子时，这不会返回日期(例如，它错过了 17-11-2009)。

是否有一些 SQL 会给我我想要的？或者我应该使用一些非 SQL 代码来处理没有帖子的日子？

Answer 1

WITH    dates AS
        (
        SELECT  CAST('2009-01-01' AS DATETIME) AS d
        UNION ALL
        SELECT  DATEADD(day, 1, d)
        FROM    dates
        WHERE   d <= GETDATE()
        )
SELECT  d, COUNT(posts.id)
FROM    dates
LEFT JOIN
        posts
ON      posts.date >= d
        AND posts.date < DATEADD(day, 1, d)
GROUP BY
        d
OPTION (MAXRECURSION 0)