mysql 一个月没有记录_MySQL选择每月记录，即使数据不存在

小编典典

关于效率，我不会说太多，因为我没有针对其他方法进行过测试，但是如果没有临时表，这似乎是一个不错的选择。

SELECT COUNT(u.userID) AS total, m.month

FROM (

SELECT 'Jan' AS MONTH

UNION SELECT 'Feb' AS MONTH

UNION SELECT 'Mar' AS MONTH

UNION SELECT 'Apr' AS MONTH

UNION SELECT 'May' AS MONTH

UNION SELECT 'Jun' AS MONTH

UNION SELECT 'Jul' AS MONTH

UNION SELECT 'Aug' AS MONTH

UNION SELECT 'Sep' AS MONTH

UNION SELECT 'Oct' AS MONTH

UNION SELECT 'Nov' AS MONTH

UNION SELECT 'Dec' AS MONTH

) AS m

LEFT JOIN users u

ON MONTH(STR_TO_DATE(CONCAT(m.month, ' 2013'),'%M %Y')) = MONTH(u.userRegistredDate)

AND YEAR(u.userRegistredDate) = '2013'

GROUP BY m.month

ORDER BY 1+1;

如果您基于日期格式进行并集，甚至可以减少工作量和查询负担。

SELECT COUNT(u.userID) AS total, DATE_FORMAT(merge_date,'%b') AS month, YEAR(m.merge_date) AS year

FROM (

SELECT '2013-01-01' AS merge_date

UNION SELECT '2013-02-01' AS merge_date

UNION SELECT '2013-03-01' AS merge_date

UNION SELECT '2013-04-01' AS merge_date

UNION SELECT '2013-05-01' AS merge_date

UNION SELECT '2013-06-01' AS merge_date

UNION SELECT '2013-07-01' AS merge_date

UNION SELECT '2013-08-01' AS merge_date

UNION SELECT '2013-09-01' AS merge_date

UNION SELECT '2013-10-01' AS merge_date

UNION SELECT '2013-11-01' AS merge_date

UNION SELECT '2013-12-01' AS merge_date

) AS m

LEFT JOIN users u

ON MONTH(m.merge_date) = MONTH(u.userRegistredDate)

AND YEAR(m.merge_date) = YEAR(u.userRegistredDate)

GROUP BY m.merge_date

ORDER BY 1+1;

2020-05-17