SQL Server按月份分组统计符合条件的事件数问题

先通过CTE生成当年12个月份的起始、结束日期，再关联事件表筛选符合重叠条件的记录，最后按月份分组统计数量。

完整查询语句 #

WITH YearMonths AS (
    SELECT 
        MONTH(MonthStart) AS MonthNum,
        DATENAME(MONTH, MonthStart) AS MonthName,
        MonthStart,
        EOMONTH(MonthStart) AS MonthEnd
    FROM (
        SELECT DATEFROMPARTS(YEAR(GETDATE()), 1, 1) AS MonthStart
        UNION ALL SELECT DATEFROMPARTS(YEAR(GETDATE()), 2, 1)
        UNION ALL SELECT DATEFROMPARTS(YEAR(GETDATE()), 3, 1)
        UNION ALL SELECT DATEFROMPARTS(YEAR(GETDATE()), 4, 1)
        UNION ALL SELECT DATEFROMPARTS(YEAR(GETDATE()), 5, 1)
        UNION ALL SELECT DATEFROMPARTS(YEAR(GETDATE()), 6, 1)
        UNION ALL SELECT DATEFROMPARTS(YEAR(GETDATE()), 7, 1)
        UNION ALL SELECT DATEFROMPARTS(YEAR(GETDATE()), 8, 1)
        UNION ALL SELECT DATEFROMPARTS(YEAR(GETDATE()), 9, 1)
        UNION ALL SELECT DATEFROMPARTS(YEAR(GETDATE()), 10, 1)
        UNION ALL SELECT DATEFROMPARTS(YEAR(GETDATE()), 11, 1)
        UNION ALL SELECT DATEFROMPARTS(YEAR(GETDATE()), 12, 1)
    ) AS Months
)
SELECT 
    ym.MonthName AS 月份,
    COUNT(DISTINCT e.EventId) AS 事件数量
FROM YearMonths ym
LEFT JOIN Events e
    ON e.startTime <= ym.MonthEnd
    AND e.endTime >= ym.MonthStart
    AND YEAR(e.startTime) = YEAR(GETDATE()) -- 仅统计当年事件
GROUP BY ym.MonthNum, ym.MonthName
ORDER BY ym.MonthNum;

关键逻辑说明 #

• YearMonths CTE：生成当年1-12月的月初、月末日期，同时提取月份编号和名称，保证即使某月份无事件也能返回0值。
• 关联条件：e.startTime <= ym.MonthEnd AND e.endTime >= ym.MonthStart 确保事件与当前月份存在时间重叠，跨月事件会被关联到所有涉及的月份。
• COUNT(DISTINCT e.EventId)：避免同一事件在同一月份被重复统计，若事件表无重复记录，可直接使用COUNT(e.EventId)。
• 当年事件过滤：YEAR(e.startTime) = YEAR(GETDATE()) 限定只统计当年事件，若业务需要也可替换为YEAR(e.endTime) = YEAR(GETDATE())，根据实际场景调整。

使用时只需将语句中的Events（表名）、EventId（事件唯一标识字段）、startTime、endTime替换为你实际的表和字段名即可。

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