您可以使用月份序列和mananeg作为空值

SELECT 
    sum(case when startDate is null then 0 else 1 end)  AS count,
    CASE
        WHEN t.mon_num = 1 THEN 'JAN'
        WHEN t.mon_num = 2 THEN 'FEB'
        WHEN t.mon_num = 3 THEN 'MAR'
        WHEN t.mon_num = 4 THEN 'APR'
        WHEN t.mon_num = 5 THEN 'MAY'
        WHEN t.mon_num = 6 THEN 'JUN'
        WHEN t.mon_num = 7 THEN 'JUL'
        WHEN t.mon_num = 8 THEN 'AUG'
        WHEN t.mon_num = 9 THEN 'SEP'
        WHEN t.mon_num = 10 THEN 'OCT'
        WHEN t.mon_num = 11 THEN 'NOV'
        WHEN t.mon_num = 12 THEN 'DEC'
    END AS SMonth
FROM  (
        select  1 mon_num
        union
        select  2 
        union
        select  3 
        union
        select  4 
        union
        select  5 
        union
        select  6 
        union
        select  7 
        union
        select  8 
        union
        select  9 
        union
        select  10 
        union
        select  11 
        union
        select  12 
) t
LEFT JOIN Reports ON t.mon_num = MONTH(startDate) 
GROUP BY SMonth

像这样的东西也许合适。但是你也可以从date函数中得到month。

SELECT 
    date_table.dt,
    MONTHNAME(date_table.dt) SMonth,
    COUNT(r.startDate) AS count
FROM 
    (
        SELECT 
            DATE(CONCAT(1970 + y1.num + y2.num*10, "-", mnth.num, "-01")) dt
        FROM
            (SELECT 1 num UNION ALL SELECT 2 num UNION ALL SELECT 3 num UNION ALL SELECT 4 num UNION ALL SELECT 5 num UNION ALL SELECT 6 num UNION ALL SELECT 7 num UNION ALL SELECT 8 num UNION ALL SELECT 9 num UNION ALL SELECT 10 num) y1
        CROSS JOIN
            (SELECT 1 num UNION ALL SELECT 2 num UNION ALL SELECT 3 num UNION ALL SELECT 4 num UNION ALL SELECT 5 num UNION ALL SELECT 6 num UNION ALL SELECT 7 num UNION ALL SELECT 8 num UNION ALL SELECT 9 num UNION ALL SELECT 10 num) y2
        CROSS JOIN
            (SELECT 1 num UNION ALL SELECT 2 num UNION ALL SELECT 3 num UNION ALL SELECT 4 num UNION ALL SELECT 5 num UNION ALL SELECT 6 num UNION ALL SELECT 7 num UNION ALL SELECT 8 num UNION ALL SELECT 9 num UNION ALL SELECT 10 num UNION ALL SELECT 11 num UNION ALL SELECT 12 num) mnth
    ) date_table
LEFT JOIN 
    Reports r on FORMAT_DATE(r.startDate, "%Y-%m-01") = date_table.dt
WHERE
    date_table.dt BETWEEN "1991-07-24" AND NOW()
GROUP BY
    date_table.dt
ORDER BY
    date_table.dt
;

这种方法也很有效，你应该有最新版本的mysql。

with recursive cte (n) as (
select '2012-01-01' 
union all 
select n+interval 1 month
from cte 
where n < '2012-12-01'
) select count(emp.empno) as total,date_format(cte.n,'%M') as Month
 from cte left join emp
 on  date_format(cte.n,'%M')  = date_format(emp.hiredate,'%M') 
 group by cte.n ;

你可以用 coalesce(count(*), 0) 或者 ifnull(count(*), 0) 在您的查询中。

SELECT 
    coalesce(COUNT(*), 0) AS count,
    CASE
        WHEN MONTH(startDate) = 1 THEN 'JAN'
        WHEN MONTH(startDate) = 2 THEN 'FEB'
        WHEN MONTH(startDate) = 3 THEN 'MAR'
        WHEN MONTH(startDate) = 4 THEN 'APR'
        WHEN MONTH(startDate) = 5 THEN 'MAY'
        WHEN MONTH(startDate) = 6 THEN 'JUN'
        WHEN MONTH(startDate) = 7 THEN 'JUL'
        WHEN MONTH(startDate) = 8 THEN 'AUG'
        WHEN MONTH(startDate) = 9 THEN 'SEP'
        WHEN MONTH(startDate) = 10 THEN 'OCT'
        WHEN MONTH(startDate) = 11 THEN 'NOV'
        WHEN MONTH(startDate) = 12 THEN 'DEC'
    END AS SMonth
FROM
    Reports
GROUP BY SMonth