亲宝软件园·资讯

展开

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

Winton-Q 人气:0

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

    

  

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

 

 

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

 

 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层就不写了,是个程序员,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>

      

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

加载全部内容

相关教程
猜你喜欢
用户评论