即使資料不存在,如何在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中文網其他相關文章!