即使特定月份不存在數據，如何在 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中文網其他相關文章！