即使特定月份不存在数据，如何在 MySQL 中检索按月记录？

即使数据不存在，如何在 MySQL 中选择按月记录

从用户表中检索按月记录，您可以使用如下查询：

SELECT COUNT(`userID`) AS total, DATE_FORMAT(`userRegistredDate`, '%b') AS MONTH, YEAR(`userRegistredDate`) AS year
FROM `users`
GROUP BY DATE_FORMAT(FROM_UNIXTIME(`userRegistredDate`, '%b'))

此查询将为您提供用户总数及其注册月份和年份。但是，它只会显示有现有数据的月份。

要包含没有数据的月份，您可以使用以下方法之一：

方法 1 ：月份并集

此方法涉及创建所需年份中所有可能月份的并集：

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;

方法 2：日期并集

此方法创建所需年份中所有月份的日期并集：

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;

两种并集方法都会为所有月份创建占位符，允许您显示记录，即使数据不存在。两种方法的选择取决于您的具体要求。

以上是即使特定月份不存在数据，如何在 MySQL 中检索按月记录？的详细内容。更多信息请关注PHP中文网其他相关文章！