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' ) ) 输出：
total MONTH year
3 May 2013 2 Jul 2013
预期产量：
total MONTH year
0 Jan 2013 0 Feb 2013 0 Mar 2013 0 Apr 2013 3 May 2013 0 Jun 2013 2 Jul 2013
即使数据不存在，我也需要显示记录。这个怎么做？
写回答
-
关于效率，我不会说太多，因为我没有针对其他方法进行过测试，但是如果没有临时表，这似乎是一个不错的选择。
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;来源：stack overflow
2020-05-17 20:30:58赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
