MYSQL统计最近一年/一周的数据，要求没有数据的日子或者月份，要显示为0

最新推荐文章于 2024-04-20 23:52:41 发布

丿剑过血洒泪轻吟

最新推荐文章于 2024-04-20 23:52:41 发布

阅读量457

点赞数

文章标签： mysql java

本文链接：https://blog.youkuaiyun.com/weixin_46200547/article/details/117442557

版权

最近一周（其中t_dc_bi为我的表名，CREATE_TIME为我的时间字段名）

SELECT a.day,IFNULL(b.total , 0) total
FROM  
	(SELECT DATE_FORMAT(CURDATE(), '%Y-%m-%d') AS `day`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 DAY), '%Y-%m-%d') AS `day`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 DAY), '%Y-%m-%d') AS `day`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 DAY), '%Y-%m-%d') AS `day`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 DAY), '%Y-%m-%d') AS `day`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 DAY), '%Y-%m-%d') AS `day`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 DAY), '%Y-%m-%d') AS `day`
	) a 
LEFT JOIN 
	(SELECT DATE_FORMAT(t.`CREATE_TIME`,'%Y-%m-%d') DAY,COUNT(t.id) total
	FROM `t_dc_bi` t WHERE
	DATE_FORMAT(t.`CREATE_TIME`,'%Y-%m-%d')>
	DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 7 DAY),'%Y-%m-%d')
	GROUP BY DAY) b
 ON a.day=b.day

最近一年（其中t_dc_data_set为我的表名，CREATE_TIME为我的时间字段名）

SELECT a.month,IFNULL(b.total , 0) total
FROM  
	(SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `month`
	) a 
LEFT JOIN 
	(SELECT DATE_FORMAT(t.`CREATE_TIME`,'%Y-%m') MONTH,COUNT(t.id) total
	FROM `t_dc_data_set` t WHERE
	DATE_FORMAT(t.`CREATE_TIME`,'%Y-%m')>
	DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH),'%Y-%m')
	GROUP BY MONTH) b
 ON a.month=b.month