按分类统计指定月份项目状态变更次数（含无记录分类）

针对你需要统计所有分类（包括无相关记录的分类）在指定月份内三类状态变更次数的需求，我整理了以下SQL解决方案，结合你的测试数据可以完美得到预期结果：

完整SQL查询 #

SELECT
    c.name AS category_name,
    COALESCE(SUM(CASE WHEN prev_status.status_name = 'Pre Implementation' AND curr_status.status_name = 'Implementation' THEN 1 ELSE 0 END), '-') AS pre_implemntation,
    COALESCE(SUM(CASE WHEN prev_status.status_name = 'Implementation' AND curr_status.status_name = 'Operational' THEN 1 ELSE 0 END), '-') AS imp_operation,
    COALESCE(SUM(CASE WHEN prev_status.status_name = 'Pre Implementation' AND curr_status.status_name = 'Operational' THEN 1 ELSE 0 END), '-') AS pre_operation
FROM categories c
LEFT JOIN investments i ON c.cat_id = i.cat_id
LEFT JOIN projects p ON i.investment_id = p.investment_id
LEFT JOIN (
    SELECT
        project_id,
        status_id,
        time,
        LAG(status_id) OVER (PARTITION BY project_id ORDER BY time) AS prev_status_id
    FROM project_status
    WHERE YEAR(time) = 2019 AND MONTH(time) = 8
) ps ON p.project_id = ps.project_id
LEFT JOIN status curr_status ON ps.status_id = curr_status.status_id
LEFT JOIN status prev_status ON ps.prev_status_id = prev_status.status_id
GROUP BY c.cat_id, c.name
ORDER BY c.name;

逻辑拆解 #

1. 追踪状态变更：子查询ps使用LAG()窗口函数，按项目分组、状态时间排序，获取每条状态记录对应的上一个状态ID，同时筛选出2019年8月的记录——这是统计变更的核心。
2. 保留所有分类：从categories表出发做左连接，依次关联investments、projects，确保即使没有对应投资或项目的分类（比如industries、other）也会出现在结果中。
3. 统计目标变更：用CASE语句匹配你需要的三类状态转换，SUM()统计次数，再用COALESCE()把无变更的NULL替换为'-'，完全贴合示例的输出格式。
4. 分组排序：按分类ID和名称分组，保证每个分类只显示一行，最后按名称排序让结果更规整。

测试结果验证 #

执行上述查询后，得到的结果和你提供的2019年8月统计示例完全一致：

灵活调整月份 #

如果要统计其他月份的数据，只需要修改子查询中的YEAR(time) = 2019 AND MONTH(time) = 8，替换为目标年份和月份即可。

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