mysql 按照月份统计 使用 case when

表结构

CREATE TABLE `custom_table_casebase` (
  `id` varchar(32) NOT NULL,
  `create_date` varchar(64) DEFAULT NULL,
  `create_user` varchar(32) DEFAULT NULL,
  `dept_id` varchar(64) DEFAULT NULL,
  `status` varchar(32) DEFAULT '未发起',
  `start_users` text,
  `deviceName` varchar(255) DEFAULT NULL COMMENT ' 装置名称 ',
  `major` varchar(255) DEFAULT NULL COMMENT ' 专业 ',
  `enterpriseName` varchar(255) DEFAULT NULL COMMENT ' 企业名称 ',
  `enterpriseNumber` varchar(255) DEFAULT NULL COMMENT ' 企业编号 ',
  `caseNo` varchar(255) DEFAULT NULL COMMENT ' 案例编号 ',
  `problemDescription` text COMMENT ' 问题描述与分析 ',
  `problemNature` text COMMENT '问题性质',
  `dealAdvice` text COMMENT '处理建议',
  `problemDate` varchar(19) DEFAULT '' COMMENT '问题日期',
  `uploadPictures` text COMMENT '上传图片',
  `uploadVideo` text COMMENT '上传视频',
  `equipmentNameNumber` text COMMENT ' 设备名称/位号 ',
  `dataSource` text COMMENT ' 数据源 ',
  PRIMARY KEY (`id`),
  UNIQUE KEY `caseNo` (`caseNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='案例库';

sql 

        SELECT
            IFNULL( SUM( CASE WHEN date_format( create_date, '%m' ) = 01 THEN 1 ELSE 0 END ), 0 ) AS keys1,
            IFNULL( SUM( CASE WHEN date_format( create_date, '%m' ) = 02 THEN 1 ELSE 0 END ), 0 ) AS keys2,
            IFNULL( SUM( CASE WHEN date_format( create_date, '%m' ) = 03 THEN 1 ELSE 0 END ), 0 ) AS keys3,
            IFNULL( SUM( CASE WHEN date_format( create_date, '%m' ) = 04 THEN 1 ELSE 0 END ), 0 ) AS keys4,
            IFNULL( SUM( CASE WHEN date_format( create_date, '%m' ) = 05 THEN 1 ELSE 0 END ), 0 ) AS keys5,
            IFNULL( SUM( CASE WHEN date_format( create_date, '%m' ) = 06 THEN 1 ELSE 0 END ), 0 ) AS keys6,
            IFNULL( SUM( CASE WHEN date_format( create_date, '%m' ) = 07 THEN 1 ELSE 0 END ), 0 ) AS keys7,
            IFNULL( SUM( CASE WHEN date_format( create_date, '%m' ) = 08 THEN 1 ELSE 0 END ), 0 ) AS keys8,
            IFNULL( SUM( CASE WHEN date_format( create_date, '%m' ) = 09 THEN 1 ELSE 0 END ), 0 ) AS keys9,
            IFNULL( SUM( CASE WHEN date_format( create_date, '%m' ) = 10 THEN 1 ELSE 0 END ), 0 ) AS keys10,
            IFNULL( SUM( CASE WHEN date_format( create_date, '%m' ) = 11 THEN 1 ELSE 0 END ), 0 ) AS keys11,
            IFNULL( SUM( CASE WHEN date_format( create_date, '%m' ) = 12 THEN 1 ELSE 0 END ), 0 ) AS keys12
        FROM
            custom_table_casebase
        WHERE
            date_format( create_date, '%Y' ) = date_format( #{map.time}, '%Y' )

定义一个返回实体

@Data
public class KeyValueDTO {

    private String keys1;
    private String keys2;
    private String keys3;
    private String keys4;
    private String keys5;
    private String keys6;
    private String keys7;
    private String keys8;
    private String keys9;
    private String keys10;
    private String keys11;
    private String keys12;


}

service实现层 ，前端需要返回一个数组

    public R statisticsLineChart(Map<String, Object> map) {
        if( null ==  map || null ==  map.get("time") ){
             map.put("time", DateUtils.getCurrentTimeString());
        }
        List<String> list = new ArrayList<>();
        KeyValueDTO kv  = homeMapper.statisticsAllCountGroupMonth(map);
        list.add( kv.getKeys1() );
        list.add( kv.getKeys2() );
        list.add( kv.getKeys3() );
        list.add( kv.getKeys4() );
        list.add( kv.getKeys5() );
        list.add( kv.getKeys6() );
        list.add( kv.getKeys7() );
        list.add( kv.getKeys8() );
        list.add( kv.getKeys9() );
        list.add( kv.getKeys10() );
        list.add( kv.getKeys11() );
        list.add( kv.getKeys12() );
        return R.ok().data( list );
    }