如何用SQL统计Stack Exchange中Perl6标签每月问题帖总数

我帮你把这段SQL完善好了，先看最终可用的版本：

WITH QuestStatsByMonth AS (
    SELECT 
        DATEADD(month, DATEDIFF(month, 0, q.CreationDate), 0) AS [Month],
        t.TagName,
        SUM(q.Score) AS TotalScore,
        COUNT(q.Id) AS TotalQuestions
    FROM Posts q
    INNER JOIN PostTags pt ON q.Id = pt.PostId
    INNER JOIN Tags t ON t.Id = pt.TagId
    WHERE q.PostTypeId = 1 
      AND t.TagName IN ('perl6')
    GROUP BY DATEADD(month, DATEDIFF(month, 0, q.CreationDate), 0), t.TagName
)
SELECT 
    [Month],
    CONCAT(TagName, ' questions') AS [Tag],
    TotalQuestions AS [Total Posts],
    TotalScore AS [Total Score]
FROM QuestStatsByMonth
ORDER BY [Month];

主要修改与说明： #

• 删掉了原CTE里的q.Id：Stack Exchange用的是SQL Server，它要求聚合查询里的非聚合字段必须出现在GROUP BY中，留着单个问题ID不仅没用还会报错
• 新增COUNT(q.Id) AS TotalQuestions：直接在分组时统计每个月的问题总数，这正是你需要的核心统计数据
• 主查询补充了完整字段展示，还加了ORDER BY [Month]让结果按时间顺序排列，阅读起来更直观

如果觉得CTE有点冗余，也可以用更简洁的写法，效果完全一致：

SELECT 
    DATEADD(month, DATEDIFF(month, 0, q.CreationDate), 0) AS [Month],
    CONCAT(t.TagName, ' questions') AS [Tag],
    COUNT(q.Id) AS [Total Posts],
    SUM(q.Score) AS [Total Score]
FROM Posts q
INNER JOIN PostTags pt ON q.Id = pt.PostId
INNER JOIN Tags t ON t.Id = pt.TagId
WHERE q.PostTypeId = 1
  AND t.TagName = 'perl6'
GROUP BY DATEADD(month, DATEDIFF(month, 0, q.CreationDate), 0), t.TagName
ORDER BY [Month];

额外验证点： #

• PostTypeId = 1确实是Stack Exchange里标记"问题帖"的字段值，这个筛选条件没问题
• 日期截断写法DATEADD(month, DATEDIFF(month, 0, q.CreationDate), 0)是SQL Server中把日期统一到当月第一天的标准操作，能确保同一月份的问题被正确分组

内容的提问来源于stack exchange，提问作者jjmerelo