MySQL查询近7天、1年、5年内数据，无数据部分补0

概述

需求为查询数据库中7天内的数据，并按天统计；查询数据库中12个月的数据，并按月份统计；查询数据库中5年内的数据，并按年统计；

思路

首先获取7日、1年、5年的数据时间，将该数据时间做成一个临时表，再通过LEFT JOIN ···ON ···去关联实际需要查询的数据表；

数据库测试数据截图

7天内数据查询及补0

原SQL查询 无数据补0

--  7天内查询
select id,today_in, create_date from test_time
where create_date >DATE_SUB(CURDATE(), INTERVAL 7  DAY);

查询结果

可以看到我当前测试的时间为2021-12-21，那么7日内就是2021-12-15至2021-12-21，其中有12-18至12-21是没有数据的，我们就需要进行补0的操作

SQL数据补0操作

首先获取7天内所有日期

SELECT @cdate := date_add( @cdate, INTERVAL - 1 DAY ) date 
FROM ( SELECT @cdate := date_add( CURDATE(), INTERVAL 1 DAY ) FROM test_time LIMIT 7 ) a

查询结果

整合

首先将上面获取7日内时间的SQL作为一个临时数据集，然后左连接原来的SQL查询语句

-- 7天内按天统计 补0
SELECT IFNULL( b.today_in, 0 ) today_in,aa.date create_date
FROM (SELECT @cdate := date_add( @cdate, INTERVAL - 1 DAY ) date 
			FROM ( SELECT @cdate := date_add( CURDATE(), INTERVAL 1 DAY ) FROM test_time LIMIT 8 ) a 
			) aa LEFT JOIN (select id,today_in, create_date from test_time
							where create_date >=DATE_SUB(CURDATE(), INTERVAL 7  DAY)
							) b on aa.date = b.create_date
ORDER BY aa.date DESC;

查询结果

可以看到12-18至12-21没有数据的部分已经自动补0

1年内数据查询按月统计及补0

原SQL查询 无数据补0

-- 1年内按月统计
SELECT sum(today_in) today_in,mon create_date 
FROM (SELECT today_in,create_date, DATE_FORMAT(create_date,'%Y-%m') mon
			FROM test_time WHERE create_date >= DATE_SUB(CURDATE(),INTERVAL 1 YEAR)) a
GROUP BY mon DESC;

查询结果

可以看到只有11和12月有数据，1月至10月没有数据则没有查询结果

SQL数据补0操作

首先获取1年内所有月份

-- 一年所有月份
SELECT DATE_FORMAT( @cdate := date_add( @cdate, INTERVAL - 1 MONTH ), '%Y-%m' ) date 
FROM ( SELECT @cdate := date_add( CURDATE(), INTERVAL 1 MONTH ) FROM test_time  ) a

查询结果

整合

-- 1年内按月统计 补0
SELECT IFNULL( b.today_in, 0 ) today_in,aa.date create_date
FROM (SELECT DATE_FORMAT( @cdate := date_add( @cdate, INTERVAL - 1 MONTH ), '%Y-%m' ) date 
				FROM ( SELECT @cdate := date_add( CURDATE(), INTERVAL 1 MONTH ) FROM test_time  ) a
			) aa LEFT JOIN (SELECT sum(today_in) today_in,mon create_date 
											FROM (SELECT today_in,create_date, DATE_FORMAT(create_date,'%Y-%m') mon
														FROM test_time WHERE create_date >= DATE_SUB(CURDATE(),INTERVAL 1 YEAR)) a
														GROUP BY mon DESC
												) b on aa.date = b.create_date;

查询结果

5年内数据查询按年统计及补0

原SQL查询 无数据补0

--  5年内按年统计
SELECT sum(today_in) today_in,year create_date 
FROM (SELECT today_in, DATE_FORMAT(create_date,'%Y') year
			FROM test_time WHERE create_date >= DATE_SUB(CURDATE(),INTERVAL 5 YEAR)) a
GROUP BY year;

查询结果

可以看到只有18年至21年有数据，7年没有数据则没有查询结果

SQL数据补0操作

首先获取5年内所有年份

-- 五年所有年份
SELECT DATE_FORMAT( @cdate := date_add( @cdate, INTERVAL - 1 YEAR ), '%Y' ) date 
FROM ( SELECT @cdate := date_add( CURDATE(), INTERVAL 1 YEAR ) FROM test_time LIMIT 5) a

查询结果

整合

-- 5年内按年统计 补0
SELECT IFNULL( b.today_in, 0 ) today_in,aa.date create_date
FROM (SELECT DATE_FORMAT( @cdate := date_add( @cdate, INTERVAL - 1 YEAR ), '%Y' ) date 
			FROM ( SELECT @cdate := date_add( CURDATE(), INTERVAL 1 YEAR ) FROM test_time LIMIT 5) a ) aa
						LEFT JOIN (SELECT sum(today_in) today_in,year create_date 
												FROM (SELECT today_in, DATE_FORMAT(create_date,'%Y') year
															FROM test_time WHERE create_date >= DATE_SUB(CURDATE(),INTERVAL 5 YEAR)) a
												GROUP BY year) b on aa.date = b.create_date;

查询结果

总结

主要先将需要查询的时间段内所有时间查询出来并作为临时数据表，在通过日期关联左连接即可。

函数	描述
IFNULL(expression, alt_value)	如果第一个参数的表达式 expression 为 NULL，则返回第二个参数的备用值
关键字	描述
LEFT JOIN ···ON···	从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL