MySQL统计近7天（两周、一个月等）数据，没有数据显示为0

数据库

CREATE TABLE `sys_user` (
  `id` varchar(255) NOT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `user_age` int DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



INSERT INTO `sys_user` (`id`, `user_name`, `user_age`, `create_time`) VALUES ('1', '张三', NULL, '2022-05-14 09:51:49');


CREATE TABLE `test` (
  `id` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test` (`id`) VALUES ('1');
INSERT INTO `test` (`id`) VALUES ('2');
INSERT INTO `test` (`id`) VALUES ('3');
INSERT INTO `test` (`id`) VALUES ('4');
INSERT INTO `test` (`id`) VALUES ('5');
INSERT INTO `test` (`id`) VALUES ('6');
INSERT INTO `test` (`id`) VALUES ('7');
INSERT INTO `test` (`id`) VALUES ('8');

方法一：

​ 使用函数，创建一张临时表，并往里面加入查询范围内日期

DROP PROCEDURE IF EXISTS FillDateTable;

delimiter //
CREATE PROCEDURE FillDateTable(startTime varchar(20), endTime varchar(20))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  drop table if exists datetable;
  create TEMPORARY table datetable (dateNum datetime primary key, isweekday smallint);

  SET @x := date(startTime);
  REPEAT 
    insert into datetable (dateNum, isweekday) SELECT @x, case when dayofweek(@x) in (1,7) then 0 else 1 end;
    SET @x := date_add(@x, interval 1 day);
    UNTIL @x >= endTime END REPEAT;
END//
delimiter ;

CALL FillDateTable('2022-05-12', '2022-05-18');

-- 近7天
select
	DATE_FORMAT(dt.dateNum,'%Y-%m-%d') as date,
	IFNULL(table_user.num, 0) as num
from
	datetable dt
left join  (
	select DATE_FORMAT(su.create_time,'%Y-%m-%d') as createTime, COUNT(1) as num from sys_user su  
	where
		su.create_time BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND DATE_SUB(CURDATE(),INTERVAL 1 DAY)
)table_user on dt.dateNum = table_user.createTime
 where
 	dt.dateNum BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND DATE_SUB(CURDATE(),INTERVAL 1 DAY)
group by dt.dateNum

根据 FillDateTable（开始时间，结束时间），传入自己所要查询的日期范围。

查询结果

方法二：

获取近7天日期

SELECT @cdate := DATE_ADD(@cdate, INTERVAL 1 DAY) cdate
FROM
	(
		SELECT
			@cdate := DATE_SUB(CURDATE(), INTERVAL 8 DAY)
		FROM
			(
				SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
			) b
	) a

SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 生成一列7行的数据

注意：如果超过 7 天，查询 n 天的数据 ，那就要先生成一列 n 行的数据，可以使用其他有数据表代替（如下），最蠢的办法就是 union  

SELECT @cdate := DATE_ADD(@cdate, INTERVAL 1 DAY) cdate
FROM
	(
		SELECT
			@cdate := DATE_SUB(CURDATE(), INTERVAL 8 DAY)
		FROM
			(
				SELECT * FROM test limit 7 
			) b
	) a

注意：必须保证参照表有大于或等于索要查询天数的数据量

-- union 方式
SELECT
	table_date.cdate,
	IFNULL(table_user.num, 0) nums
FROM
(
	SELECT
		 @cdate := DATE_ADD(@cdate, INTERVAL 1 DAY) cdate
		FROM
		 (
			SELECT
			 @cdate := DATE_SUB(CURDATE(), INTERVAL 8 DAY)
			FROM
			 	(
				SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
             	) b
			) a
) table_date
LEFT JOIN 
 (
    select DATE_FORMAT(su.create_time,'%Y-%m-%d') createTime, COUNT(su.id) num from sys_user su
 		where DATE_FORMAT(su.create_time,'%Y-%m-%d') BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) and  DATE_SUB(CURDATE(), INTERVAL 1 DAY)
 ) table_user  ON table_date.cdate = table_user.createTime
 WHERE
 	-- 不加也行，上面已经是取7天的数据
 	table_date.cdate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY table_date.cdate;


-- 参照表 方式
SELECT
	table_date.cdate,
	IFNULL(table_user.num, 0) nums
FROM
(
	SELECT
		 @cdate := DATE_ADD(@cdate, INTERVAL 1 DAY) cdate
		FROM
		 (
			SELECT
			 @cdate := DATE_SUB(CURDATE(), INTERVAL 8 DAY)
			FROM
			 	(
				SELECT * FROM test limit 7 
             	) b
			) a
) table_date
LEFT JOIN 
 (
    select DATE_FORMAT(su.create_time,'%Y-%m-%d') createTime, COUNT(su.id) num from sys_user su
 		where DATE_FORMAT(su.create_time,'%Y-%m-%d') BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) and  DATE_SUB(CURDATE(), INTERVAL 1 DAY)
 ) table_user  ON table_date.cdate = table_user.createTime
 WHERE
 	-- 不加也行，上面已经是取7天的数据
 	table_date.cdate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY table_date.cdate;

查询结果

部分筛选条件

-- 获取上个月
DATE_FORMAT(su.create_time,'%Y-%m') =  DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 month),'%Y-%m')

-- 获取本月截止到昨天
DATE_FORMAT(su.create_time,'%Y-%m-%d') BETWEEN concat(DATE_FORMAT(now(),'%Y-%m'), '-01') AND  DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d')

-- 获取今年截至到昨天
DATE_FORMAT(su.create_time,'%Y-%m-%d') BETWEEN concat(DATE_FORMAT(now(),'%Y'), '-01-01') AND  DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d')

-- 获取近7天
su.create_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND DATE_SUB(CURDATE(), INTERVAL 1 DAY)