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

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

    

  

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

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

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

 -- 会员充值表
CREATE TABLE rechargeinfo(
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) REFERENCES operatorinfo(o_id)
)ENGINE = InnoDB COMMENT '会员充值信息表';
alter table rechargeinfo change r_time r_time timestamp not null default NOW(); --停车登记表 CREATE TABLE parkinginfo(
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) as Jan,
sum(case month(r_time) when '' then r_recharge else 0 end) as Feb,
sum(case month(r_time) when '' then r_recharge else 0 end) as Mar,
sum(case month(r_time) when '' then r_recharge else 0 end) as Apr,
sum(case month(r_time) when '' then r_recharge else 0 end) as May,
sum(case month(r_time) when '' then r_recharge else 0 end) as June,
sum(case month(r_time) when '' then r_recharge else 0 end) as July,
sum(case month(r_time) when '' then r_recharge else 0 end) as Aug,
sum(case month(r_time) when '' then r_recharge else 0 end) as Sept,
sum(case month(r_time) when '' then r_recharge else 0 end) as Oct,
sum(case month(r_time) when '' then r_recharge else 0 end) as Nov,
sum(case month(r_time) when '' then r_recharge else 0 end) as Dece
from rechargeinfo
where year(r_time)='';

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

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

   

   接下来给出的是Dao层代码,service层就不写了,是个程序员,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层:

 //统计页面
@RequestMapping("/totalui")
public ModelAndView test(@RequestParam(value ="toyear",required = false,defaultValue = "2019")String toyear){
ModelAndView mv = new ModelAndView();
// get all data
Map<String, Double> rechargeTotal = service.getRechargeTotal(toyear);
Map<String,Double> pachargeTotal = service.getParkingTotal(toyear);
// test
System.out.println("测试所有数据: "+rechargeTotal.values()+" ");
System.out.println("测试所有数据 : "+ pachargeTotal.values()+" "); // 统计
Map<String,Double> 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");
return mv; }

    前端页面显示图代码:

 <script>
var chart = 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
}
}
}]
}
}); </script>

      

  大功告成,不懂得可以直接评论咨询!!!!!

上一篇:方法重写和方法重载;this关键字和super关键字


下一篇:微信小程序怎么用?线下商家最适合玩小程序