MySQL中使用count() over()实现按状态和月份统计的需求问题

云上老周

2026-5-8

解决MySQL统计报表不符合预期的问题

问题分析

你的原始查询核心问题是没有先对status和月份进行分组聚合，而是直接对每条原始记录使用窗口函数。窗口函数会为每行计算全局/分组的统计值，再加上DISTINCT只能去重，无法得到你想要的「状态+月份」维度的聚合结果。最终导致输出的是每条记录的窗口统计值，而非每个组合的实际计数。

修正后的SQL语句

我们需要先通过GROUP BY按status和月份分组，得到每个组合的当月记录数，再用窗口函数计算月份总数、总记录数等全局统计值：

SELECT
    status AS Status,
    COUNT(*) AS StatusCount,
    CASE 
        WHEN MONTH(requestedDate) = 1 THEN 'JAN' 
        WHEN MONTH(requestedDate) = 2 THEN 'FEB' 
        WHEN MONTH(requestedDate) = 3 THEN 'MAR' 
        WHEN MONTH(requestedDate) = 4 THEN 'APR' 
        WHEN MONTH(requestedDate) = 5 THEN 'MAY' 
        WHEN MONTH(requestedDate) = 6 THEN 'JUN' 
        WHEN MONTH(requestedDate) = 7 THEN 'JUL' 
        WHEN MONTH(requestedDate) = 8 THEN 'AUG' 
        WHEN MONTH(requestedDate) = 9 THEN 'SEP' 
        WHEN MONTH(requestedDate) = 10 THEN 'OCT' 
        WHEN MONTH(requestedDate) = 11 THEN 'NOV' 
        WHEN MONTH(requestedDate) = 12 THEN 'DEC' 
    END AS Month,
    COUNT(*) OVER (PARTITION BY MONTH(requestedDate)) AS MonthCount,
    SUM(COUNT(*)) OVER () AS CountTotal
FROM Reports
WHERE requestedDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 120 DAY) AND CURDATE()
GROUP BY status, MONTH(requestedDate)
ORDER BY Status, MONTH(requestedDate);