Mysql统计每年每个月的数据(前端页面统计图实现)
最终想实现的效果图,在这里就不多废话了,直接上效果图,由于测试数据有几个月是为0的,所以数据图看着会有点怪怪。
接下来是数据库的两个表,这里直接给你们代码了,你们根据自己的需求更改即可
--会员充值表
CREATE TABLErechargeinfo( r_num INT PRIMARY KEY COMMENT '充值编号', v_card VARCHAR(12) COMMENT '会员卡号', r_recharge DOUBLE COMMENT '充值金额', r_payway VARCHAR(20) COMMENT '支付方式', o_id INT COMMENT '操作员工编号', r_time DATETIME COMMENT '交易时间', r_remark VARCHAR(50) COMMENT '交易备注', FOREIGN KEY (o_id) REFERENCESoperatorinfo(o_id) )ENGINE = InnoDB COMMENT '会员充值信息表'; alter table rechargeinfo change r_time r_time timestamp not null defaultNOW();
--停车登记表
CREATE TABLEparkinginfo( p_num INT PRIMARY KEY COMMENT '停车编号', c_carplate VARCHAR(20) NOT NULL COMMENT '车牌号', p_card VARCHAR(20) COMMENT '停车牌号', p_picture VARCHAR(50) COMMENT '进场拍摄图', p_entrytime Date COMMENT '进场时间', p_leavetime Date COMMENT '出场时间', p_type VARCHAR(10) COMMENT '客户类型', p_cost Double COMMENT '停车费用', p_payway VARCHAR(20) COMMENT '支付方式', v_card VARCHAR(12) COMMENT '会员卡号', v_phone VARCHAR(12) COMMENT '临时客户手机号码', p_condition VARCHAR(20) DEFAULT '正在停车中' COMMENT '状态', p_remark VARCHAR(50) COMMENT '备注'
)ENGINE = InnoDB COMMENT '停车信息表'; alter table parkinginfo change p_entrytime p_entrytime timestamp not null default NOW();
接下来就是重点了:
SQL语句,只需要传入一个参数(年份)即可, 这个是统计会员充值表的,另一张表同理
select
sum(case month(r_time) when '' then r_recharge else 0 end) asJan, sum(case month(r_time) when '' then r_recharge else 0 end) asFeb, sum(case month(r_time) when '' then r_recharge else 0 end) asMar, sum(case month(r_time) when '' then r_recharge else 0 end) asApr, sum(case month(r_time) when '' then r_recharge else 0 end) asMay, sum(case month(r_time) when '' then r_recharge else 0 end) asJune, sum(case month(r_time) when '' then r_recharge else 0 end) asJuly, sum(case month(r_time) when '' then r_recharge else 0 end) asAug, sum(case month(r_time) when '' then r_recharge else 0 end) asSept, sum(case month(r_time) when '' then r_recharge else 0 end) asOct, sum(case month(r_time) when '' then r_recharge else 0 end) asNov, sum(case month(r_time) when '' then r_recharge else 0 end) asDece fromrechargeinfo where year(r_time)='';
效果图:可以看到,每个月的数据已经查出来了
接下来给出的是Dao层代码,service层就不写了,是个程序员,dao层知道,service都会写
importjava.util.Map;public interfaceTotalDao {
MapgetRechargeTotal(String toyear);
MapgetParkingTotal(String toyear);
}
以及Mapper文件代码:注意,这里的结果类型一定要是java.util.LinkedHashMap, 如果是HashMap,是不会报错,但是顺序会乱,So,,你懂得。
select
sum(case month(r_time) when '1' then r_recharge else 0 end) as Jan,
sum(case month(r_time) when '2' then r_recharge else 0 end) as Feb,
sum(case month(r_time) when '3' then r_recharge else 0 end) as Mar,
sum(case month(r_time) when '4' then r_recharge else 0 end) as Apr,
sum(case month(r_time) when '5' then r_recharge else 0 end) as May,
sum(case month(r_time) when '6' then r_recharge else 0 end) as June,
sum(case month(r_time) when '7' then r_recharge else 0 end) as July,
sum(case month(r_time) when '8' then r_recharge else 0 end) as Aug,
sum(case month(r_time) when '9' then r_recharge else 0 end) as Sept,
sum(case month(r_time) when '10' then r_recharge else 0 end) as Oct,
sum(case month(r_time) when '11' then r_recharge else 0 end) as Nov,
sum(case month(r_time) when '12' then r_recharge else 0 end) as Dece
from rechargeinfo
where year(r_time)=#{toyear};
select
sum(case month(p_leavetime) when '1' then p_cost else 0 end) as Jan,
sum(case month(p_leavetime) when '2' then p_cost else 0 end) as Feb,
sum(case month(p_leavetime) when '3' then p_cost else 0 end) as Mar,
sum(case month(p_leavetime) when '4' then p_cost else 0 end) as Apr,
sum(case month(p_leavetime) when '5' then p_cost else 0 end) as May,
sum(case month(p_leavetime) when '6' then p_cost else 0 end) as June,
sum(case month(p_leavetime) when '7' then p_cost else 0 end) as July,
sum(case month(p_leavetime) when '8' then p_cost else 0 end) as Aug,
sum(case month(p_leavetime) when '9' then p_cost else 0 end) as Sept,
sum(case month(p_leavetime) when '10' then p_cost else 0 end) as Oct,
sum(case month(p_leavetime) when '11' then p_cost else 0 end) as Nov,
sum(case month(p_leavetime) when '12' then p_cost else 0 end) as Dece
from parkinginfo
where year(p_leavetime)=#{toyear} and p_condition='交易完成' ;
Control层:
//统计页面
@RequestMapping("/totalui") public ModelAndView test(@RequestParam(value ="toyear",required = false,defaultValue = "2019")String toyear){ ModelAndView mv = newModelAndView(); //get all data
Map rechargeTotal =service.getRechargeTotal(toyear); Map pachargeTotal =service.getParkingTotal(toyear); //test
System.out.println("测试所有数据: "+rechargeTotal.values()+" "); System.out.println("测试所有数据 : "+ pachargeTotal.values()+" ");
//统计
Map datatotal = new LinkedHashMap<>(); for(String key:rechargeTotal.keySet()){ if(pachargeTotal.containsKey(key)){ datatotal.put(key, rechargeTotal.get(key)+pachargeTotal.get(key)); } } System.out.println("合并后的数据!!!"+datatotal.values());
//set atrr
mv.addObject("redata",rechargeTotal.values()); mv.addObject("padata",pachargeTotal.values()); mv.addObject("totaldata",datatotal.values()); mv.setViewName("Income"); returnmv;
}
前端页面显示图代码:
varchart=Highcharts.chart('container', { chart: { type:'column'
}, title: { text:'洱海湾停车场营业额统计'
},
legend: { align:'right', verticalAlign:'middle', layout:'vertical'
}, xAxis: { categories: ['一月','二月','三月','四月','五月','六月','七月','八月','九月','十月','十一月','十二月'], labels: { x:-10
} }, yAxis: { allowDecimals:false, title: { text:'金额'
} }, series: [{ name:'会员充值收入', data: ${redata} }, { name:'停车收入', data: ${padata} }, { name:'合计收入', data: ${totaldata} }], responsive: { rules: [{ condition: { maxWidth:1500
}, chartOptions: { legend: { align:'center', verticalAlign:'bottom', layout:'horizontal'
}, yAxis: { labels: { align:'left', x:0, y:-5
}, title: { text:'¥金额以元为单位'
} }, subtitle: { text:null
}, credits: { enabled:false
} } }] } });
大功告成,不懂得可以直接评论咨询!!!!!