mysql - 如何在mysql查询中显示计数为零

Question

我想获得在特定月份加入的员工人数。以下是我的示例数据。

如果一个月内没有员工加入，则查询应显示为零，如下所示，

我想出了这样的东西，但这仅显示特定月份有加入者的月份，

SELECT 
    COUNT(*) 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

我正在使用mysql。

编辑

Reports 表包含不同年份的数据，例如 1988、2012、2015 等的数据，因此可能有 2015 年 1 月的加入者数据，但 2018 年 1 月没有

标签： mysqlsql

Answer 1

类似的东西可能适合。但是您也可以从日期函数中获取月份。

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
;