如何按月统计Projects表中的活跃项目（含跨月项目）？

你的问题核心在于之前的统计逻辑只覆盖了当月启动或当月结束的项目，但忽略了那些横跨多个月份的项目——这类项目在中间的每个月都属于活跃状态，必须被计入对应月份的统计。

无需拆分多个SELECT语句的关键思路是：先构建一个完整的目标月份序列，再将每个月份与项目表关联，判断项目是否在该月份内处于活跃状态。

具体实现步骤 #

1. 生成连续的月份列表 #

首先需要生成你要统计的所有年月（比如从最早的项目启动月到当前月），这里用CTE递归实现，适配大多数主流SQL数据库：

WITH months AS (
    -- 起始月份：取Projects表中最早的start_date对应的年月
    SELECT DATE_TRUNC('month', MIN(start_date)) AS month
    FROM Projects
    UNION ALL
    -- 递归生成后续每个月，直到当前月份
    SELECT DATE_ADD(month, INTERVAL 1 MONTH)
    FROM months
    WHERE month < DATE_TRUNC('month', CURRENT_DATE)
)

2. 关联项目表统计活跃数 #

将生成的月份表和Projects表关联，用统一规则判断每个项目是否在对应月份活跃：

• 活跃判定规则：项目的start_date ≤ 该月份的最后一天，且end_date ≥ 该月份的第一天

完整SQL语句如下：

WITH months AS (
    SELECT DATE_TRUNC('month', MIN(start_date)) AS month
    FROM Projects
    UNION ALL
    SELECT DATE_ADD(month, INTERVAL 1 MONTH)
    FROM months
    WHERE month < DATE_TRUNC('month', CURRENT_DATE)
)
SELECT
    m.month AS year_month,
    COUNT(p.id) AS active_projects
FROM months m
LEFT JOIN Projects p
    ON p.start_date <= LAST_DAY(m.month)
    AND p.end_date >= m.month
GROUP BY m.month
ORDER BY m.month;

关键细节说明 #

• 月份边界处理：不同数据库的日期函数略有差异，比如PostgreSQL可以用(DATE_TRUNC('month', m.month) + INTERVAL '1 month - 1 day')替代LAST_DAY，SQL Server用EOMONTH(m.month)即可
• LEFT JOIN保证全量月份：即使某个月没有活跃项目，也会返回0，避免遗漏统计月份
• 性能优化：如果Projects表数据量较大，建议给start_date和end_date建立联合索引，能大幅加快关联查询的速度

这样一套语句就能一次性统计所有月份的活跃项目，无需拆分多个SELECT再关联求和，完美覆盖所有跨月的活跃场景。

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