mysql按天查询数据，没有数据就补0

最新推荐文章于 2024-09-12 15:19:45 发布

原创最新推荐文章于 2024-09-12 15:19:45 发布 · 2k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

文章讲述了如何在SQL中将时间戳转换为日期格式，并在数据查询时处理缺少日期的情况。通过创建虚拟表获取指定月份的所有日期，然后使用LEFTJOIN和IFNULL函数确保无数据的日期显示为0。

首选这是我自己想要的数据，我的日期数据是时间戳，所以要转换，看起来比较麻烦，

可以把 FROM_UNIXTIME(create_time/1000,'%Y-%m-%d') 看成自己正常时间

select 
    FROM_UNIXTIME(create_time/1000,'%Y-%m-%d') days,
	count(*) num 
FROM `temporary_air_route` 
where 
	air_route like '%P125, ENKUS%'	
	and FROM_UNIXTIME(create_time/1000,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m') 
group by FROM_UNIXTIME(create_time/1000,'%Y-%m-%d')

然而我的项目需求是其他日期的没有数据的要加上0

这里我们可以先查出本月的全部日期做一个虚拟表

select last_day(curdate()) date是查询本月的最后一天，

如果不想要本月的，就可以自己写上日期如 '2023-05-08'

SELECT
	@cdate := date_add( @cdate, INTERVAL - 1 DAY ) days 
FROM
	( SELECT @cdate := date_add( (select last_day(curdate()) date), INTERVAL 1 DAY ) 
FROM `temporary_air_route` LIMIT 31 ) a

这样就把一个月的日期全部查出来

然后再把两个数据做一个左连接查询

使用ifnull函数来补0

里面的where后面的条件可以根据自己的需求来写

SELECT 
	t1.*,
	ifnull(t2.num,0) num
FROM (
	SELECT
		@cdate := date_add( @cdate, INTERVAL - 1 DAY ) days 
	FROM
		( SELECT @cdate := date_add( (select last_day(curdate()) date), INTERVAL 1 DAY ) FROM `temporary_air_route` LIMIT 31 ) a
		) t1 
	left join (
	select 
		FROM_UNIXTIME(create_time/1000,'%Y-%m-%d') days,
		count(*) num 
	FROM `temporary_air_route` 
	where 
		air_route like '%P125, ENKUS%'	
		and FROM_UNIXTIME(create_time/1000,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m') 
	group by FROM_UNIXTIME(create_time/1000,'%Y-%m-%d')
	) t2 on t1.days = t2.days