Mysql统计每年每个月的数据——详细教程
Mysql统计每年每个月的数据(前端页面统计图实现)
最终想实现的效果图，在这里就不多废话了，直接上效果图，由于测试数据有几个月是为0的，所以数据图看着会有点怪怪。
另外，个人使用了ssm框架(Spring、Mybatis、SpringMVC)，可根据自己的项目情况修改
接下来是数据库的两个表，这里直接给你们代码了，你们根据自己的需求更改即可
1 -- 会员充值表 2 CREATE TABLE rechargeinfo( 3 r_num INT PRIMARY KEY COMMENT \'充值编号\', 4 v_card VARCHAR(12) COMMENT \'会员卡号\', 5 r_recharge DOUBLE COMMENT \'充值金额\', 6 r_payway VARCHAR(20) COMMENT \'支付方式\', 7 o_id INT COMMENT \'操作员工编号\', 8 r_time DATETIME COMMENT \'交易时间\', 9 r_remark VARCHAR(50) COMMENT \'交易备注\', 10 FOREIGN KEY (o_id) REFERENCES operatorinfo(o_id) 11 )ENGINE = InnoDB COMMENT \'会员充值信息表\'; 12 alter table rechargeinfo change r_time r_time timestamp not null default NOW(); 13 14 --停车登记表 15 16 CREATE TABLE parkinginfo( 17 p_num INT PRIMARY KEY COMMENT \'停车编号\', 18 c_carplate VARCHAR(20) NOT NULL COMMENT \'车牌号\', 19 p_card VARCHAR(20) COMMENT \'停车牌号\', 20 p_picture VARCHAR(50) COMMENT \'进场拍摄图\', 21 p_entrytime Date COMMENT \'进场时间\', 22 p_leavetime Date COMMENT \'出场时间\', 23 p_type VARCHAR(10) COMMENT \'客户类型\', 24 p_cost Double COMMENT \'停车费用\', 25 p_payway VARCHAR(20) COMMENT \'支付方式\', 26 v_card VARCHAR(12) COMMENT \'会员卡号\', 27 v_phone VARCHAR(12) COMMENT \'临时客户手机号码\', 28 p_condition VARCHAR(20) DEFAULT \'正在停车中\' COMMENT \'状态\', 29 p_remark VARCHAR(50) COMMENT \'备注\' 30 31 )ENGINE = InnoDB COMMENT \'停车信息表\'; 32 alter table parkinginfo change p_entrytime p_entrytime timestamp not null default NOW();
接下来就是重点了：
SQL语句，只需要传入一个参数（年份）即可， 这个是统计会员充值表的，另一张表同理
1 select 2 sum(case month(r_time) when \'1\' then r_recharge else 0 end) as Jan, 3 sum(case month(r_time) when \'2\' then r_recharge else 0 end) as Feb, 4 sum(case month(r_time) when \'3\' then r_recharge else 0 end) as Mar, 5 sum(case month(r_time) when \'4\' then r_recharge else 0 end) as Apr, 6 sum(case month(r_time) when \'5\' then r_recharge else 0 end) as May, 7 sum(case month(r_time) when \'6\' then r_recharge else 0 end) as June, 8 sum(case month(r_time) when \'7\' then r_recharge else 0 end) as July, 9 sum(case month(r_time) when \'8\' then r_recharge else 0 end) as Aug, 10 sum(case month(r_time) when \'9\' then r_recharge else 0 end) as Sept, 11 sum(case month(r_time) when \'10\' then r_recharge else 0 end) as Oct, 12 sum(case month(r_time) when \'11\' then r_recharge else 0 end) as Nov, 13 sum(case month(r_time) when \'12\' then r_recharge else 0 end) as Dece 14 from rechargeinfo 15 where year(r_time)=\'2019\';
效果图：可以看到，每个月的数据已经查出来了
接下来给出的是Dao层代码，service层就不写了
import java.util.Map; public interface TotalDao { Map<String,Double> getRechargeTotal(String toyear); Map<String,Double> getParkingTotal(String toyear); }
以及Mapper文件代码：注意，这里的结果类型一定要是java.util.LinkedHashMap, 如果是HashMap，是不会报错，但是顺序会乱，So，，你懂得。
<!-- 统计充值-->
<select id="getRechargeTotal" parameterType="String" resultType="java.util.LinkedHashMap">
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>
<!--统计停车-->
<select id="getParkingTotal" parameterType="String" resultType="java.util.LinkedHashMap">
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=\'交易完成\' ;
</select>
</mapper>
Control层：
1 //统计页面 2 @RequestMapping("/totalui") 3 public ModelAndView test(@RequestParam(value ="toyear",required = false,defaultValue = "2019")String toyear){ 4 ModelAndView mv = new ModelAndView(); 5 // get all data 6 Map<String, Double> rechargeTotal = service.getRechargeTotal(toyear); 7 Map<String,Double> pachargeTotal = service.getParkingTotal(toyear); 8 // test 9 System.out.println("测试所有数据： "+rechargeTotal.values()+" "); 10 System.out.println("测试所有数据 : "+ pachargeTotal.values()+" "); 11 12 // 统计 13 Map<String,Double> datatotal = new LinkedHashMap<>(); 14 for(String key:rechargeTotal.keySet()){ 15 if(pachargeTotal.containsKey(key)){ 16 datatotal.put(key, rechargeTotal.get(key)+pachargeTotal.get(key)); 17 } 18 } 19 System.out.println("合并后的数据！！！"+datatotal.values()); 20 21 // set atrr 22 mv.addObject("redata",rechargeTotal.values()); 23 mv.addObject("padata",pachargeTotal.values()); 24 mv.addObject("totaldata",datatotal.values()); 25 mv.setViewName("Income"); 26 return mv; 27 28 }
前端页面显示图代码：
1 <script> 2 var chart = Highcharts.chart(\'container\', { 3 chart: { 4 type: \'column\' 5 }, 6 title: { 7 text: \'洱海湾停车场营业额统计\' 8 }, 9 10 legend: { 11 align: \'right\', 12 verticalAlign: \'middle\', 13 layout: \'vertical\' 14 }, 15 xAxis: { 16 categories: [\'一月\', \'二月\', \'三月\', \'四月\', \'五月\', \'六月\', \'七月\', \'八月\', \'九月\', \'十月\', \'十一月\', \'十二月\'], 17 labels: { 18 x: -10 19 } 20 }, 21 yAxis: { 22 allowDecimals: false, 23 title: { 24 text: \'金额\' 25 } 26 }, 27 series: [{ 28 name: \'会员充值收入\', 29 data: ${redata} 30 }, { 31 name: \'停车收入\', 32 data: ${padata} 33 }, { 34 name: \'合计收入\', 35 data: ${totaldata} 36 }], 37 responsive: { 38 rules: [{ 39 condition: { 40 maxWidth: 1500 41 }, 42 chartOptions: { 43 legend: { 44 align: \'center\', 45 verticalAlign: \'bottom\', 46 layout: \'horizontal\' 47 }, 48 yAxis: { 49 labels: { 50 align: \'left\', 51 x: 0, 52 y: -5 53 }, 54 title: { 55 text: \'￥金额以元为单位\' 56 } 57 }, 58 subtitle: { 59 text: null 60 }, 61 credits: { 62 enabled: false 63 } 64 } 65 }] 66 } 67 }); 68 69 </script>
大功告成，不懂得可以直接评论咨询！！！！！
版权声明：本文为winton-nfs原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。