查询近12个月的数据，没有数据的月份补零

可以有两种方法实现，一种是纯sql的方式，一种是sql和代码共用的形式

一、纯sql的形式

前半部分用于一个近12个月的表，然后再将查出来的数据和月份表相结合，就是想要的结果

select v.month,b.price price,ifnull(b.count,0)  count 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`
) v 
left join
(select 
 left(create_time,7) as 'month',plant_name,SUM(IFNULL(product_price,0)) price,SUM(IFNULL(out_count,0)) as count
from clesun_farmer.storage_out_record as a	
		where DATE_FORMAT(a.create_time,'%Y-%m')>
		DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m')
		AND a.farm_id = 1603
		GROUP BY month
)b
on v.month = b.month group by v.month,b.plant_name

二、sql和java代码结合的方式

1.先用sql查出来结果，然后在用代码获取12个月的时间，然后和查询的数据进行拼接

这里我先放一个方法用于计算近12个月的时间方法，可以把它写成一个工具类，这里我就不写了

/**
	 * 获取最近12个月份
	 * @return
	 */
	public static List<String> getLatest12Month(){
		List<String> months = new ArrayList<>();
		String[] latest12Months = new String[12];
		Calendar cal = Calendar.getInstance();
		//要先+1,才能把本月的算进去
		cal.set(Calendar.MONTH, cal.get(Calendar.MONTH)+1);
		for(int i=0; i< 12; i++){
			//逐次往前推1个月
			cal.set(Calendar.MONTH, cal.get(Calendar.MONTH)-1);
			latest12Months[11-i] = cal.get(Calendar.YEAR)+ "-" +fillZero(cal.get(Calendar.MONTH)+1);
		}
		for (String s : latest12Months) {
			months.add(s);
		}
		return months;
	}

	/**
	 * 格式化月份
	 * @param i
	 * @return
	 */
	public static String fillZero(int i){
		String month = "";
		if(i<10){
			month = "0" + i;
		}else{
			month = String.valueOf(i);
		}
		return month;
	}

下面是我进行拼接时用的方法

Map<String, Object> resultMap = new HashMap<>(16);
        //获取当前12个月的月份
        List<String> months = DateUtil.getLatest12Month();
        List<String> XData = Lists.newArrayList();
        List<String> yData = Lists.newArrayList();
        //根据farmId获取该厂区下地块的总种植面积
        Double totalArea = baseMapper.areaByFarmId(farmId);
        //根据farmId获取每月种植地块的面积和次数
        List<Map<String,Object>> areaMonthly =plantMapper.areaMonthlyByFarmId(farmId);

        //先给每个月都赋值后面覆盖
        List<Map<String,Object>> dates = new ArrayList<>();
        for (String month : months) {
            Map<String, Object> monthMap = new HashMap<>();
            monthMap.put("month",month);
            monthMap.put("data","0.00");
            dates.add(monthMap);
        }
        //计算使用率
        if (!StringUtils.isEmpty(areaMonthly)){
            for (Map<String, Object> date : dates) {
                for (Map<String, Object> map : areaMonthly) {
                    if (date.get("month").equals(map.get("month"))){
                        //使用率保留2位小数
                        Double rate = (Double.parseDouble(String.valueOf(map.get("plantArea"))) / Double.parseDouble(String.valueOf(map.get("count"))) / totalArea)*100;
                        BigDecimal b = new BigDecimal(rate);
                        rate = b.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                        date.put("data",rate);
                    }
                }
            }
            //封装数据
            for (Map<String, Object> map : dates) {
                XData.add(String.valueOf(map.get("month")));
                yData.add(String.valueOf(String.valueOf(map.get("data"))));
            }
        }
        resultMap.put("XData",XData);
        resultMap.put("yData",yData);