mysql 统计每年的数据统计_Mysql统计每年每个月的数据——详细教程

最新推荐文章于 2023-12-26 15:48:49 发布

泡沫桃子

最新推荐文章于 2023-12-26 15:48:49 发布

阅读量399

点赞数

文章标签： mysql 统计每年的数据统计

本文链接：https://blog.csdn.net/weixin_33754535/article/details/113301812

版权

Mysql统计每年每个月的数据(前端页面统计图实现)

最终想实现的效果图，在这里就不多废话了，直接上效果图，由于测试数据有几个月是为0的，所以数据图看着会有点怪怪。

接下来是数据库的两个表，这里直接给你们代码了，你们根据自己的需求更改即可

1 --会员充值表

2 CREATE TABLErechargeinfo(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) REFERENCESoperatorinfo(o_id)11 )ENGINE = InnoDB COMMENT '会员充值信息表';12 alter table rechargeinfo change r_time r_time timestamp not null defaultNOW();13

14 --停车登记表

16 CREATE TABLEparkinginfo(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 '备注'

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) asJan,3 sum(case month(r_time) when '2' then r_recharge else 0 end) asFeb,4 sum(case month(r_time) when '3' then r_recharge else 0 end) asMar,5 sum(case month(r_time) when '4' then r_recharge else 0 end) asApr,6 sum(case month(r_time) when '5' then r_recharge else 0 end) asMay,7 sum(case month(r_time) when '6' then r_recharge else 0 end) asJune,8 sum(case month(r_time) when '7' then r_recharge else 0 end) asJuly,9 sum(case month(r_time) when '8' then r_recharge else 0 end) asAug,10 sum(case month(r_time) when '9' then r_recharge else 0 end) asSept,11 sum(case month(r_time) when '10' then r_recharge else 0 end) asOct,12 sum(case month(r_time) when '11' then r_recharge else 0 end) asNov,13 sum(case month(r_time) when '12' then r_recharge else 0 end) asDece14 fromrechargeinfo15 where year(r_time)='2019';

效果图：可以看到，每个月的数据已经查出来了

接下来给出的是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层：

1 //统计页面

2 @RequestMapping("/totalui")3 public ModelAndView test(@RequestParam(value ="toyear",required = false,defaultValue = "2019")String toyear){4 ModelAndView mv = newModelAndView();5 //get all data

6 Map rechargeTotal =service.getRechargeTotal(toyear);7 Map pachargeTotal =service.getParkingTotal(toyear);8 //test

9 System.out.println("测试所有数据： "+rechargeTotal.values()+" ");10 System.out.println("测试所有数据 : "+ pachargeTotal.values()+" ");11

12 //统计

13 Map 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 returnmv;27

28 }

前端页面显示图代码：

2 varchart=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

大功告成，不懂得可以直接评论咨询！！！！！