按月份分组并使用别名统计表单状态结果异常求助

你的SQL子查询未与外层查询的月份做关联，每个子查询都是统计全表对应状态的总数量，因此每个月份行返回的数值完全一致。

方案一：关联子查询（修复原有结构） #

给每个子查询添加月份匹配条件，确保统计的是当前月份的对应状态数据：

SELECT MONTHNAME(date_added) as month,
       (SELECT COUNT(*) FROM requests r2 
        WHERE r2.form_status = :approved 
          AND MONTHNAME(r2.date_added) = MONTHNAME(r1.date_added)
          AND r2.req_dept = :req_dept) AS approved, 
       (SELECT COUNT(*) FROM requests r2 
        WHERE r2.form_status = :denied 
          AND MONTHNAME(r2.date_added) = MONTHNAME(r1.date_added)
          AND r2.req_dept = :req_dept) AS denied, 
       (SELECT COUNT(*) FROM requests r2 
        WHERE r2.form_status = :completed 
          AND MONTHNAME(r2.date_added) = MONTHNAME(r1.date_added)
          AND r2.req_dept = :req_dept) AS completed,
       (SELECT COUNT(*) FROM requests r2 
        WHERE r2.form_status = :pending 
          AND MONTHNAME(r2.date_added) = MONTHNAME(r1.date_added)
          AND r2.req_dept = :req_dept) AS pending 
FROM requests r1 
WHERE r1.req_dept = :req_dept 
GROUP BY month

注：若存在跨年度的同月份数据，建议同时用YEAR(date_added)和MONTH(date_added)关联，避免不同年份的同月份数据被错误合并。

方案二：条件聚合（更高效简洁） #

使用CASE WHEN结合聚合函数，仅需一次表扫描即可完成统计，性能更优：

SELECT 
    MONTHNAME(date_added) as month,
    COUNT(CASE WHEN form_status = :approved THEN 1 END) AS approved,
    COUNT(CASE WHEN form_status = :denied THEN 1 END) AS denied,
    COUNT(CASE WHEN form_status = :completed THEN 1 END) AS completed,
    COUNT(CASE WHEN form_status = :pending THEN 1 END) AS pending
FROM requests 
WHERE req_dept = :req_dept 
GROUP BY MONTH(date_added), MONTHNAME(date_added)

注：添加MONTH(date_added)分组是为了保证月份按1-12的顺序排列；跨年度场景需将YEAR(date_added)加入SELECT和GROUP BY，区分不同年份的同月份数据。

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