java实现日报表、月报表统计，没数据补0 |
您所在的位置：网站首页 › 数据库报表统计 › java实现日报表、月报表统计，没数据补0 |
| 产品需求：
1、日报表 image.png2、月报表 image.png需求点： 前端传日期或月份区间，当数据库中指定的日期或月份没数据时也需要界面显示，但领取人数和使用人数需要自动补齐0； 举例：2020-11-28、2020-11-29，数据库中没数据，但也需要在界面显示，领取人数和使用人数为0； image.png 思路很重要1、首先根据前端传来的时间区间，查询出区间中所有的日期或月份； 2、然后循环日期或月份和数据库查询返回的List的日期或月份进行匹配； 2.1、不存在则在List中新增一条实体对象数据； 3、最后统一把处理后的List返回前端； sql语句（后面Mapper.xml中全部详细列出）注：这里是Mysql数据库，以日报表为例，** 月报表则是将 DATE_FORMAT格式修改为 %Y-%m 即可 ** SELECT t.days, sum( t.receiveQty ) AS receiveQty, sum( t.userQty ) AS userQty FROM ( SELECT DATE_FORMAT( r.created_time, '%Y-%m-%d' ) AS days, count( r.id ) AS receiveQty, 0 AS userQty FROM dg_experience_receive r LEFT JOIN dg_experience_order o ON r.user_id = o.user_id WHERE DATE_FORMAT( r.created_time, '%Y-%m-%d' ) >= '2020-11-01' AND DATE_FORMAT( r.created_time, '%Y-%m-%d' ) SELECT DATE_FORMAT( r.created_time, '%Y-%m-%d' ) AS days, 0 AS receiveQty, count( r.id ) AS userQty FROM dg_experience_receive r JOIN dg_experience_order o ON r.user_id = o.user_id WHERE r.is_used = 1 AND DATE_FORMAT( r.created_time, '%Y-%m-%d' ) = '2020-11-01' AND DATE_FORMAT( r.created_time, '%Y-%m-%d' ) dateStr.equals(item.getDays())).findAny().isPresent()){ DepositExperienceDayRes experienceDayRes = DepositExperienceDayRes.builder() .days(dateStr) .receiveQty(0L) .userQty(0L) .build(); experienceDayResList.add(experienceDayRes); } } List collect = experienceDayResList.stream().sorted(Comparator.comparing(DepositExperienceDayRes::getDays).reversed()).collect(Collectors.toList()); return collect; } /** * 运营报表--体验金月报表--查询 * @param experienceDayReq * @return */ @Override public List listExperienceMonthByReq(DepositExperienceDayReq experienceDayReq) { // 首先根据前端传来的时间区间，查询出区间中所有的月份； DateTime startDate = DateUtil.parse(experienceDayReq.getStartDate(), "yyyy-MM"); DateTime endDate = DateUtil.parse(experienceDayReq.getEndDate(), "yyyy-MM"); List lDate = findDates(startDate, endDate, Calendar.MONTH); if (CollectionUtils.isEmpty(lDate)){ return null; } List experienceDayResList = baseMapper.listExperienceMonthByReq(experienceDayReq); for (Date date : lDate){ // 当前日期不存在则新增 String dateStr = DateUtil.format(date, "yyyy-MM"); // 进行匹配 if(!experienceDayResList.stream().filter(item -> dateStr.equals(item.getDays())).findAny().isPresent()){ DepositExperienceDayRes experienceDayRes = DepositExperienceDayRes.builder() .days(dateStr) .receiveQty(0L) .userQty(0L) .build(); experienceDayResList.add(experienceDayRes); } } List collect = experienceDayResList.stream().sorted(Comparator.comparing(DepositExperienceDayRes::getDays).reversed()).collect(Collectors.toList()); return collect; } /** * 获取指定时间区间的所有数据（包含日期和月份） * @param dBegin * @param dEnd * @param rule 日历规则 如：Calendar.DAY_OF_MONTH * @return */ public static List findDates(Date dBegin, Date dEnd, int rule) { List lDate = new ArrayList(); if (dEnd.before(dBegin)){ return lDate; } lDate.add(dBegin); Calendar calBegin = Calendar.getInstance(); // 使用给定的 Date 设置此 Calendar 的时间 calBegin.setTime(dBegin); Calendar calEnd = Calendar.getInstance(); // 使用给定的 Date 设置此 Calendar 的时间 calEnd.setTime(dEnd); // 测试此日期是否在指定日期之后 while (dEnd.after(calBegin.getTime())) { // 根据日历的规则，为给定的日历字段添加或减去指定的时间量 calBegin.add(rule, 1); lDate.add(calBegin.getTime()); } return lDate; }Mapper.xml SELECT t.days, sum(t.receiveQty) as receiveQty, sum(t.userQty) as userQty FROM ( SELECT DATE_FORMAT(r.created_time , '%Y-%m-%d' ) AS days, count(r.id) as receiveQty, 0 as userQty FROM dg_experience_receive r LEFT JOIN dg_experience_order o on r.user_id = o.user_id = #{experienceDayReq.startDate} ]]> GROUP BY days UNION ALL SELECT DATE_FORMAT(r.created_time , '%Y-%m-%d' ) AS days, 0 as receiveQty, count(r.id) as userQty FROM dg_experience_receive r JOIN dg_experience_order o on r.user_id = o.user_id AND r.is_used = 1 = #{experienceDayReq.startDate} ]]> GROUP BY days ) t GROUP BY days order by days desc SELECT t.days, sum(t.receiveQty) as receiveQty, sum(t.userQty) as userQty FROM ( SELECT DATE_FORMAT(r.created_time , '%Y-%m' ) AS days, count(r.id) as receiveQty, 0 as userQty FROM dg_experience_receive r LEFT JOIN dg_experience_order o on r.user_id = o.user_id = #{experienceDayReq.startDate} ]]> GROUP BY days UNION ALL SELECT DATE_FORMAT(r.created_time , '%Y-%m' ) AS days, 0 as receiveQty, count(r.id) as userQty FROM dg_experience_receive r JOIN dg_experience_order o on r.user_id = o.user_id AND r.is_used = 1 = #{experienceDayReq.startDate} ]]> GROUP BY days ) t GROUP BY days order by days desc PostMan结果展示： image.png image.png |
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |
| CopyRight 2018-2019 实验室设备网 版权所有 win10的实时保护怎么永久关闭 |