echart图表显示12个月的数据

1.MySQL server的语句

--  查询某年的12个月的数据
SELECT
    sum( CASE WHEN MONTH ( o.create_time ) = 1 THEN 1 ELSE 0 END ) AS '1月',
    sum( CASE WHEN MONTH ( o.create_time ) = 2 THEN 1 ELSE 0 END ) AS '2月',
    sum( CASE WHEN MONTH ( o.create_time ) = 3 THEN 1 ELSE 0 END ) AS '3月',
    sum( CASE WHEN MONTH ( o.create_time ) = 4 THEN 1 ELSE 0 END ) AS '4月',
    sum( CASE WHEN MONTH ( o.create_time ) = 5 THEN 1 ELSE 0 END ) AS '5月',
    sum( CASE WHEN MONTH ( o.create_time ) = 6 THEN 1 ELSE 0 END ) AS '6月',
    sum( CASE WHEN MONTH ( o.create_time ) = 7 THEN 1 ELSE 0 END ) AS '7月',
    sum( CASE WHEN MONTH ( o.create_time ) = 8 THEN 1 ELSE 0 END ) AS '8月',
    sum( CASE WHEN MONTH ( o.create_time ) = 9 THEN 1 ELSE 0 END ) AS '9月',
    sum( CASE WHEN MONTH ( o.create_time ) = 10 THEN 1 ELSE 0 END ) AS '10月',
    sum( CASE WHEN MONTH ( o.create_time ) = 11 THEN 1 ELSE 0 END ) AS '11月',
    sum( CASE WHEN MONTH ( o.create_time ) = 12 THEN 1 ELSE 0 END ) AS '12月'
FROM
    orders o 
WHERE
    DATE_FORMAT( o.create_time, '%Y' ) = YEAR ( now( ) ) 
    AND o.isdelete != '1' 
    AND o.STATUS = '1'

2.mapper接口

    /**
     * 获取订单的1-12月完成的数据
     * @param orders
     * @return
     */
    public Map<String,Object> getMonthData(@Param("orders") Orders orders);

3.mapper.xml

   <select id="getMonthData" resultType="map">
        SELECT
            sum( CASE WHEN MONTH ( o.create_time ) = 1 THEN 1 ELSE 0 END ) AS '1月',
            sum( CASE WHEN MONTH ( o.create_time ) = 2 THEN 1 ELSE 0 END ) AS '2月',
            sum( CASE WHEN MONTH ( o.create_time ) = 3 THEN 1 ELSE 0 END ) AS '3月',
            sum( CASE WHEN MONTH ( o.create_time ) = 4 THEN 1 ELSE 0 END ) AS '4月',
            sum( CASE WHEN MONTH ( o.create_time ) = 5 THEN 1 ELSE 0 END ) AS '5月',
            sum( CASE WHEN MONTH ( o.create_time ) = 6 THEN 1 ELSE 0 END ) AS '6月',
            sum( CASE WHEN MONTH ( o.create_time ) = 7 THEN 1 ELSE 0 END ) AS '7月',
            sum( CASE WHEN MONTH ( o.create_time ) = 8 THEN 1 ELSE 0 END ) AS '8月',
            sum( CASE WHEN MONTH ( o.create_time ) = 9 THEN 1 ELSE 0 END ) AS '9月',
            sum( CASE WHEN MONTH ( o.create_time ) = 10 THEN 1 ELSE 0 END ) AS '10月',
            sum( CASE WHEN MONTH ( o.create_time ) = 11 THEN 1 ELSE 0 END ) AS '11月',
            sum( CASE WHEN MONTH ( o.create_time ) = 12 THEN 1 ELSE 0 END ) AS '12月'
        FROM
            orders o
        WHERE
            DATE_FORMAT( o.create_time, '%Y' ) = YEAR ( now( ) )
            AND o.isdelete != '1'
            AND o.STATUS = '1'

    </select>

4.service接口

    /**
     * 获取订单的1-12月完成的数据
     * @param orders
     * @return
     */
    public List<Integer> getMonthData(Orders orders);

5.serviceImpl

   @Override
    public List<Integer> getMonthData(Orders orders) {
        //获取数据库中的12个月的总数据，但是并没有按照1-12个月进行排序
        Map<String, Object> monthData = baseMapper.getMonthData(orders);

        //对数据进行升序排序
        List<Map.Entry<String,Object>> list=new ArrayList<Map.Entry<String,Object>>(monthData.entrySet());
        Collections.sort(list, new Comparator<Map.Entry<String, Object>>() {
            @Override
            public int compare(Map.Entry<String, Object> o1, Map.Entry<String, Object> o2) {
                //把获取的月份中的月去除在进行排序
                return Integer.valueOf(o1.getKey().replace("月","")) - Integer.valueOf(o2.getKey().replace("月",""));
            }
        });

        //返回排好的数据
        List<Integer> addList=new ArrayList<>();

        //进行对比排序
        for (Map.Entry<String,Object> m : list){
            //monthData.get(m.getKey())是个object对象不能直接转integer,先把对象转换成String类型
            addList.add(Integer.valueOf(monthData.get(m.getKey()).toString()));
        }
        return addList;
    }

6.controller

    /**
     * 获取已完成订单的12个月的数据
     * @param orders
     * @param request
     * @return
     */
    @RequestMapping("/getMonthData")
    @ResponseBody
    public JSONObject getMonthData(Orders orders,HttpServletRequest request){
        JSONObject jo=new JSONObject();
        List<Integer> monthData = ordersService.getMonthData(orders);
        jo.put("code","0");
        jo.put("data",monthData);
        return jo;
    }

7.html界面+ajax

                    <div class="layui-col-md12">
                        <div class="layui-card">
                            <div class="layui-card-header"><i class="fa fa-line-chart icon"></i>每月订单报表统计</div>
                            <div class="layui-card-body">
                                <div id="echarts-records" style="width: 100%;min-height:500px"></div>
                            </div>
                        </div>
                    </div>

        /**
         * 报表功能
         */
        var echartsRecords = echarts.init(document.getElementById('echarts-records'), 'walden');
        var optionRecords = {
            tooltip: {
                trigger: 'axis'
            },
            legend: {
                data:['已完成订单数']
            },
            grid: {
                left: '3%',
                right: '4%',
                bottom: '3%',
                containLabel: true
            },
            toolbox: {
                feature: {
                    saveAsImage: {}
                }
            },
            xAxis: {
                type: 'category',
                boundaryGap: false,
                data: ['1月', '2月', '3月', '4月', '5月', '6月', '7月', '8月', '9月', '10月', '11月', '12月']
            },
            yAxis: {
                type: 'value'
            },
            // series: [
            //     {
            //         name:'邮件营销',
            //         type:'line',
            //         data:[120, 132, 101, 134, 90, 230, 120, 132, 101, 134, 90, 230]
            //     }
            // ]
        };
        echartsRecords.setOption(optionRecords);

        //获取订单12个月的数据
        $.ajax({
            url: "/orders/getMonthData",
            type: "POST",
            dataType: "json",
            success: function (res) {
                optionRecords = {
                    series: [
                        {
                            name:'已完成订单数',
                            type:'line',
                            data: res.data
                        }
                    ]
                }
                echartsRecords.setOption(optionRecords);
            }
        })

        // echarts 窗口缩放自适应
        window.onresize = function(){
            echartsRecords.resize();
        }

    });