MySQL统计近7天、近6个月、近3年的记录，没有记0

测试数据表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `datetime` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, '小A', '2021-07-09 11:39:38');
INSERT INTO `test` VALUES (2, '小B', '2021-07-08 11:40:03');
INSERT INTO `test` VALUES (3, '小C', '2021-07-07 11:40:21');
INSERT INTO `test` VALUES (4, '小D', '2021-07-06 11:40:22');
INSERT INTO `test` VALUES (5, '小E', '2021-07-05 11:40:23');
INSERT INTO `test` VALUES (6, '小F', '2021-07-04 11:40:24');
INSERT INTO `test` VALUES (7, '小G', '2021-07-03 11:40:25');
INSERT INTO `test` VALUES (8, '中F', '2021-06-21 11:42:09');
INSERT INTO `test` VALUES (9, '中H', '2021-05-25 11:42:46');
INSERT INTO `test` VALUES (10, '大I', '2020-06-09 11:43:36');
INSERT INTO `test` VALUES (11, '大J', '2019-04-29 11:43:55');

SET FOREIGN_KEY_CHECKS = 1;

说明 

实际上，我们无论是统计近7天、近6个月、近3年的数据，原理都是一样。都需要构建一个日期的结果集，然后去 left join 需要统计的表，然后通过 group by 和 count 来分类汇总。

这里先说一种错误的做法，即直接根据日期，通过 left 截取出给定的日期精度来 group by，然后 count 统计汇总。示例SQL如下。

 如上图结果所示，7月10号没有数据，所以结果集中就没有该日期的数据。这显然不符合我们的要求，我们的要求是，如果没有数据，那么当天应该显示0。这也就是问题所在。

当然，如果你能确保给定的日期精度下都必然有数据，那么这种做法的结果和下面所讲的方法的结果是一样，甚至效率要比下面讲的方法要高很多，以至于我推荐你用这种方法即可。

统计近七天的数据

1、构建一个日期的结果集

SELECT CURDATE() AS datetime
union all
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS datetime
union all
SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS datetime
union all
SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS datetime
union all
SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS datetime
union all
SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS datetime
union all
SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS datetime

2、汇总数据表近7次数据（允许部分日期没有数据，没有则跳过，确保有7条数据即可） 

SELECT LEFT(datetime, 10) AS datetime, COUNT(1) AS num 
FROM test GROUP BY LEFT(datetime, 10) ORDER BY datetime DESC LIMIT 7

3、结果集左连接数据表汇总数据（完成）

SELECT t1.datetime, IFNULL(t2.num, 0) AS num FROM (
	SELECT CURDATE() AS datetime
	union all
	SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS datetime
	union all
	SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS datetime
	union all
	SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS datetime
	union all
	SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS datetime
	union all
	SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS datetime
	union all
	SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS datetime
) t1 LEFT JOIN (
	SELECT LEFT(datetime, 10) AS datetime, COUNT(1) AS num 
	FROM test GROUP BY LEFT(datetime, 10) ORDER BY datetime DESC LIMIT 7
) t2 ON t1.datetime = t2.datetime ORDER BY t1.datetime DESC

如上，正确的做法分三部分，对于不同的日期精度，我们只需修改部分参数即可。下面直接给出结果，不再分步说明。

统计近6个月的数据

SELECT t1.datetime, IFNULL(t2.num, 0) AS num FROM (
    SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS datetime
	union all
	SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS datetime
	union all
	SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS datetime
	union all
	SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS datetime
	union all
	SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS datetime
	union all
	SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS datetime
) t1 LEFT JOIN (
	SELECT LEFT(datetime, 7) AS datetime, COUNT(1) AS num 
	FROM test GROUP BY LEFT(datetime, 7) ORDER BY datetime DESC LIMIT 6
) t2 ON t1.datetime = t2.datetime ORDER BY t1.datetime DESC

统计近3年的数据

SELECT t1.datetime, IFNULL(t2.num, 0) AS num FROM (
	SELECT DATE_FORMAT(CURDATE(), '%Y') AS datetime
	union all
	SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 YEAR), '%Y') AS datetime
	union all
	SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 YEAR), '%Y') AS datetime
) t1 LEFT JOIN (
	SELECT LEFT(datetime, 4) AS datetime, COUNT(1) AS num 
	FROM test GROUP BY LEFT(datetime, 4) ORDER BY datetime DESC LIMIT 3
) t2 ON t1.datetime = t2.datetime ORDER BY t1.datetime DESC