iis 发布网站asp网站,试用网站空间,工信部备案查询网站,百度有几种推广方式可以有两种方法实现#xff0c;一种是纯sql的方式#xff0c;一种是sql和代码共用的形式
一、纯sql的形式
前半部分用于一个近12个月的表#xff0c;然后再将查出来的数据和月份表相结合#xff0c;就是想要的结果
select v.month,b.price price,ifnull(b.count,0) cou…可以有两种方法实现一种是纯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 1603GROUP 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 ListString getLatest12Month(){ListString months new ArrayList();String[] latest12Months new String[12];Calendar cal Calendar.getInstance();//要先1,才能把本月的算进去cal.set(Calendar.MONTH, cal.get(Calendar.MONTH)1);for(int i0; 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(i10){month 0 i;}else{month String.valueOf(i);}return month;}下面是我进行拼接时用的方法
MapString, Object resultMap new HashMap(16);//获取当前12个月的月份ListString months DateUtil.getLatest12Month();ListString XData Lists.newArrayList();ListString yData Lists.newArrayList();//根据farmId获取该厂区下地块的总种植面积Double totalArea baseMapper.areaByFarmId(farmId);//根据farmId获取每月种植地块的面积和次数ListMapString,Object areaMonthly plantMapper.areaMonthlyByFarmId(farmId);//先给每个月都赋值后面覆盖ListMapString,Object dates new ArrayList();for (String month : months) {MapString, Object monthMap new HashMap();monthMap.put(month,month);monthMap.put(data,0.00);dates.add(monthMap);}//计算使用率if (!StringUtils.isEmpty(areaMonthly)){for (MapString, Object date : dates) {for (MapString, 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 (MapString, 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);
