JAVA查询近12个月的数据并进行统计

业务场景

数据库中查询某一段近12个月的数据，但是有的月份无数据，或者有的月份有。如：A在2月份有数据，B在2月份没有。此时如果不从SQL层面查询出来的话则靠业务层写了。或者AB在两月都有。结果如下图1

我们需要给前端返回的数据结构如下：图2

业务层代码

/**
     * @Author: OUO
     * @DateTime: 2022/2/18 15:53
     * @Description: 某站近12个月的数量
     */
    @Override
    public Map<String, Object> getXungengl() {
        List<Map<String,Object>> mapList=new ArrayList<>();
        /** MYSQL查询出来得总数据，解雇如图1 */
        List<EnforVO> xungengl = enforcementStationMapper.getXungengl();
        /** 获取近12个月汉化时间 */
        List<String> dateList = convenienceMapper.getMonth().stream().map(ConvenienceVO::getClickDate).collect(Collectors.toList());
        /** 获取某站名称 */
        List<String> nameList = convenienceMapper.getConvenienceStation().stream().map(ConvenienceDO::getStationName).collect(Collectors.toList());
        /** 判断是否总数据为空 */
        if (CollectionUtils.isNotEmpty(xungengl)){
            /** 根据名称集合来循环 */
            for (String name : nameList) {
                /** 过滤 匹配某站名称相同的  */
                List<EnforVO> xungel = xungengl.stream().filter(item -> StrUtil.isNotEmpty(item.getStationName()) && name.equals(item.getStationName())).collect(Collectors.toList());
                /** 创建一个list String类型的数组  */
                List<String > data = new ArrayList<>();
                /** 遍历12个月时间 */
                for (String month : dateList) {
                    /** 定义布尔类型变量值方便跳出 */
                    boolean flag = false;
                    /** 循环遍历上面已经经过过滤后的总数据 */
                    for (EnforVO vo : xungel) {
                        /** 如果总数居查询的时间与当前遍历的月份时间相等 */
                        if (vo.getInspectTime().equals(month)){
                            /** 往空数组里面加入当前月份的巡更率  */
                            data.add(vo.getPercentage());
                            /** 将变量设置为true */
                            flag=true;
                            /*跳出*/
                            break;
                        }
                    }
                    /** 如果为 true，则为0，代表该站在这个月份无数据*/
                    if (!flag){
                        data.add("0");
                    }
                }
                Map<String ,Object> m = new HashMap<>();
                m.put("num",data);
                m.put("name",name);
                mapList.add(m);
            }
        }else{
            //初始化数据时如果数据库中暂无数据
            for (String name:nameList){
                List<String> data=new ArrayList<>();
                for (int i=0;i<12;i++){
                   data.add("0");
                }
                Map<String,Object> map=new HashMap<>();
                map.put("num",data);
                map.put("name",name);
                mapList.add(map);
            }
        }
        Map<String, Object> mm = new HashMap<>();
        mm.put("date",dateList);
        mm.put("name",nameList);
        mm.put("data",mapList);
        return mm;
    }

MySql代码

SELECT
	a.*,
	IFNULL( b.dis_num, 0 ) dis_nums,
	b.law_station_id,
	IFNULL( b.station_name, NULL ) station_name 
FROM
	(
SELECT
	DATE_FORMAT( curdate( ), '%Y年%m月' ) AS click_date UNION ALL
SELECT
	DATE_FORMAT( date_sub( curdate( ), INTERVAL 11 MONTH ), '%Y年%m月' ) AS click_date UNION ALL
SELECT
	DATE_FORMAT( date_sub( curdate( ), INTERVAL 10 MONTH ), '%Y年%m月' ) AS click_date UNION ALL
SELECT
	DATE_FORMAT( date_sub( curdate( ), INTERVAL 9 MONTH ), '%Y年%m月' ) AS click_date UNION ALL
SELECT
	DATE_FORMAT( date_sub( curdate( ), INTERVAL 8 MONTH ), '%Y年%m月' ) AS click_date UNION ALL
SELECT
	DATE_FORMAT( date_sub( curdate( ), INTERVAL 7 MONTH ), '%Y年%m月' ) AS click_date UNION ALL
SELECT
	DATE_FORMAT( date_sub( curdate( ), INTERVAL 6 MONTH ), '%Y年%m月' ) AS click_date UNION ALL
SELECT
	DATE_FORMAT( date_sub( curdate( ), INTERVAL 5 MONTH ), '%Y年%m月' ) AS click_date UNION ALL
SELECT
	DATE_FORMAT( date_sub( curdate( ), INTERVAL 4 MONTH ), '%Y年%m月' ) AS click_date UNION ALL
SELECT
	DATE_FORMAT( date_sub( curdate( ), INTERVAL 3 MONTH ), '%Y年%m月' ) AS click_date UNION ALL
SELECT
	DATE_FORMAT( date_sub( curdate( ), INTERVAL 2 MONTH ), '%Y年%m月' ) AS click_date UNION ALL
SELECT
	DATE_FORMAT( date_sub( curdate( ), INTERVAL 1 MONTH ), '%Y年%m月' ) AS click_date 
ORDER BY
	click_date 
	) a
	LEFT JOIN (
SELECT
	law_station_id,
	sum( case_dis_num ) dis_num,
	repDate,
	rep_user_id,
	rep_user_name,
	station_name 
FROM
	(
SELECT
	a.law_station_id,
	a.case_dis_num,
	DATE_FORMAT( a.rep_date, '%Y年%m月' ) AS repDate,
	a.rep_user_id,
	a.rep_user_name,
	b.station_name 
FROM
	convenience a
	LEFT JOIN t_enforcement_station b ON a.law_station_id = b.law_station_id 
WHERE
	DATE_SUB( CURDATE( ), INTERVAL 12 MONTH ) <= date( rep_date ) 
ORDER BY
	rep_date ASC 
	LIMIT 999999999 
	) AS ss 
GROUP BY
	repDate,
	station_name 
	) b ON a.click_date = b.repDate

总结

​ 思路在上面注释中都体现了。返回结果如图2