每个日期的项目计数，日期之间没有间隙

发布于 2024-08-12 00:36:07 字数 285 浏览 3 评论 0原文

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

我想返回一个像这样的表：

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

但我正在努力寻找正确的 SQL。我可以轻松获得按日期分组的帖子计数，但这不会返回没有帖子的日期（例如，它错过了 2009 年 11 月 17 日）。

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

原文

I have a table of posts to a website, where each entry has its creation date.

I want to return a table like:

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

But I'm struggling to find the right SQL. I can easily get a count of posts grouped by date, but this doesn't return the dates when there are no posts (eg it misses out 17-11-2009).

Is there some SQL that will give me what I want? Or should I use some non-SQL code to deal with the days with no posts?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦纸 2024-08-19 00:36:07

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)

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)

回复收藏 0 原文