近12个月份统计，多行合并列

1、近12月份统计，java代码、原生sql

    /**
     * 横坐标为最近的12个月份，纵坐标为考试人次
     *
     * @return
     */
    public Map<String, Object> getLineChart() {
        StringBuffer sb = new StringBuffer();
        Calendar cal = Calendar.getInstance();
        int month = cal.get(Calendar.MONTH) + 1;
        int year = cal.get(Calendar.YEAR);
        sb.append("select ");
        if (month < 12) {
            for (int i = (month + 1); i <= 12; i++) {
                this.getBuffer(sb, (year - 1), i);
            }
        }
        for (int i = 1; i <= month; i++) {
            this.getBuffer(sb, year, i);
        }
        sb.delete(sb.length() - 1, sb.length());
        sb.append(" from t_test_user_paper t1 left join t_test_paper t2 on t1.paper_id = t2.id ");
        Map<String, Object> map = caseJdbcTemplate.queryForMap(sb.toString());
        Map<String, Object> resMap = new HashMap<>();
        List<Object> xAxis = new ArrayList<>();
        List<Object> yAxis = new ArrayList<>();
        for (Object obj : map.keySet()) {
            xAxis.add(obj);
            yAxis.add(map.get(obj));
        }
        resMap.put("xAxis", xAxis);
        resMap.put("yAxis", yAxis);
        return resMap;
    }

    /**
     * 月份的调用
     *
     * @param sb
     * @param year
     * @param month
     * @return
     */
    private StringBuffer getBuffer(StringBuffer sb, int year, Integer month) {
        String yearMonth = year + "-";
        if (month < 10) {
            yearMonth += "0" + month;
        } else {
            yearMonth += month;
        }
        sb.append(" IFNULL(sum(case when ((t1.status=1 and FROM_UNIXTIME(t1.submit_time/1000,'%Y-%m') = '" + yearMonth + "') " +
                "or (t1.status = 0 and FROM_UNIXTIME(t2.examination_end_time/1000,'%Y-%m') = '" + yearMonth + "' " +
                "and t2.examination_end_time<REPLACE(unix_timestamp(current_timestamp(3)),'.',''))) then 1 else 0 end),0) as '" + yearMonth + "',");
        return sb;
    }

2、多行合并列

select user_name as userName,count(1) as testNum,group_concat(IFNULL(score,0) separator ',') as scores from t_test_paper GROUP BY user_name
order by submit_time