mysql查询一个时间段每天数据数量，没有显示为0 |
您所在的位置：网站首页 › 快速查询天数 › mysql查询一个时间段每天数据数量，没有显示为0 |
mysql查询一个时间段每天数据数量，没有显示为0
|
环境
jdk 1.8 mysql 5.6 mybatis 3.4.4 springboot 2.2.6 需求按照时间段来查询显示该时间段内每一天的数据量，如果某一天没有数据，显示数据量为0. 按照正常思路直接使用GROUP BY分组查询即可，运行如下sql SELECT COUNT(1) as `count`,date_format(u.createtime,'%Y-%m-%d') as `date` FROM user u GROUP BY date_format(u.createtime,'%Y-%m-%d')果然，是不行的，没有数据的天数没有显示出来 结果如下 这样我们就得到了一段连续的日期了，然后只需要关联查询即可显示了 select count(u.id) as count,s.date from (select date_add('2020-04-01',interval @i:=@i+1 day) as date from ( select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1) as tmp, (select @i:= -1) t ) s left join user u on s.date = date_format(u.createtime,'%Y-%m-%d') GROUP BY s.date查询结果
选择了java代码计算日期差，使用mybatis的 foreach 标签实现sql拼接，全部代码如下 service @Override public Object queryByDate(String[] date) { String date1 = date[0]; String date2 = date[1]; int num = calcBetweenDate(date1, date2); String[] countArr = new String[num]; return userMapper.queryByDate(date1,countArr); } public int calcBetweenDate(String start, String end) { SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); Date startDate = null; Date endDate = null; try { startDate = df.parse(start); endDate = df.parse(end); } catch (Exception e) { System.out.println("日期转换出错"); } int count = (int) ((endDate.getTime() - startDate.getTime()) / (24 * 60 * 60 * 1000)); return count; } mapper List queryByDate(@Param("date")String date, @Param("countArr")String[] countArr); xml select count(u.id) as count,s.date from (select date_add('2020-04-01',interval @i:=@i+1 day) as date from ( select 1 union all select 1 ) as tmp, (select @i:= -1) t ) s left join user u on s.date = date_format(u.createtime,'%Y-%m-%d') GROUP BY s.date 效率检测我们使用postman查询，检测效率, 查询日期 11天，耗时 10-20ms 左右
查询日期 365天，耗时 30-40ms 左右 查询日期 3650天，耗时 222ms 左右 查询日期 5000天，爆炸。。 Cause: java.sql.SQLException: Thread stack overrun: 246144 bytes used of a 262144 byte stack, and 16000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack. ; uncategorized SQLException; SQL state [HY000]; error code [1436]; Thread stack overrun: 246144 bytes used of a 262144 byte stack, and 16000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.; nested exception is java.sql.SQLException: Thread stack overrun: 246144 bytes used of a 262144 byte stack, and 16000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.] 3662 注意事项本方式仅适用于小范围时间查询，查询时间在上方，建议自己测试，若天数间隔大于10年及以上，可能会导致mybatis报错，若长时间段查询不建议使用此方式！ |
现在还有一个问题，如何控制生成多少个日期，也就是union all select 1 的个数，
【本文地址】