mysql - 如果没有数据，如何从mysql中按月获取数据

Question

我有一个表格，里面有日期明智的数据，即使没有数据，我如何才能按月获取数据。我试过了，但我只能获取数据库中的可用数据，但我需要所有月份的数据，如果数据不存在。我试过这个查询

select
    count(date) as absentdays,
    MONTHNAME(date) as monthname,
    MONTH(date) as monthnumber
from `tbl_records`
where FIND_IN_SET(2920,users_absent)
GROUP BY YEAR(date), MONTH(date)

我需要这样的数据

Answer 1

你可以使用 UNION 来做到这一点；)

SELECT SUM(absentdays) AS absentdays, `MONTHNAME`, monthnumber
FROM (
    SELECT COUNT(DATE) AS absentdays, MONTHNAME(DATE) AS `MONTHNAME`, MONTH(DATE) AS monthnumber
    FROM `tbl_records`
    WHERE FIND_IN_SET(2920,users_absent)
    GROUP BY YEAR(DATE), MONTH(DATE)
    UNION SELECT 0 AS absentdays, 'January' AS MONTHNAME, 1 AS monthnumber
    UNION SELECT 0 AS absentdays, 'February' AS MONTHNAME, 2 AS monthnumber
    UNION SELECT 0 AS absentdays, 'March' AS MONTHNAME, 3 AS monthnumber
    UNION SELECT 0 AS absentdays, 'April' AS MONTHNAME, 4 AS monthnumber
    UNION SELECT 0 AS absentdays, 'May' AS MONTHNAME, 5 AS monthnumber
    UNION SELECT 0 AS absentdays, 'June' AS MONTHNAME, 6 AS monthnumber
    UNION SELECT 0 AS absentdays, 'July' AS MONTHNAME, 7 AS monthnumber
    UNION SELECT 0 AS absentdays, 'August' AS MONTHNAME, 8 AS monthnumber
    UNION SELECT 0 AS absentdays, 'September' AS MONTHNAME, 9 AS monthnumber
    UNION SELECT 0 AS absentdays, 'October' AS MONTHNAME, 10 AS monthnumber
    UNION SELECT 0 AS absentdays, 'November' AS MONTHNAME, 11 AS monthnumber
    UNION SELECT 0 AS absentdays, 'December' AS MONTHNAME, 12 AS monthnumber
) t
GROUP BY `MONTHNAME`, monthnumber