Mybatis 行专列详解

1、原来是下图的效果

2、需要根据日期转成下图这样的效果，由于是静态SQL，所以日期只有两天的，后面会说到动态SQL

3、行专列SQL如下

SELECT sch_name, 

    MAX(CASE class_date WHEN '2019-10-08' THEN num ELSE 0 END) '2019-10-08',

    MAX(CASE class_date WHEN '2019-10-09' THEN num ELSE 0 END) '2019-10-09'

FROM scheduling_check

WHERE sch_name != ""

GROUP BY sch_name;

4、如果需要动态SQL，在Mapper中使用@Select拼接SQL，用foreach遍历日期，用List<Map<String, Object>>接收；

4.1 Mapper

    @Select({"<script>",
                "SELECT sch_name,",
                "<foreach index = \"index\" item = \"item\" collection = \"param.classDates\" open= \"\" separator=\",\" close=\"\">",
                    "MAX(CASE class_date WHEN #{item} THEN num ELSE 0 END) as #{item}",
                "</foreach>",
                "FROM scheduling_check ",
                "WHERE sch_name != \"\" ",
                "GROUP BY sch_name",
            "</script>"})
    List<Map<String, Object>> listSchedulingCheck(@Param("param") SchedulingCheckSql param);

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class SchedulingCheckSql {

    @ApiModelProperty("日期")
    private List<String> classDates;
}

4.2 ServiceImpl

    @Override
    public List<Map<String, Object>> listSchedulingCheck(@Param("param") SchedulingCheckSql param) {
        return baseMapper.listSchedulingCheck(param);
    }

4.3 Service

    List<Map<String, Object>> listSchedulingCheck(@Param("param") SchedulingCheckSql param);

5、Controller调用后，返回的数据效果是这样子的
 

{
	"data": [
		{
			"2020-04-01": 0,
			"2020-04-03": 1,
			"2020-04-02": 0,
			"sch_name": "A班",
			"2020-04-05": 0,
			"2020-04-04": 1
		},
		{
			"2020-04-01": 0,
			"2020-04-03": 0,
			"2020-04-02": 0,
			"sch_name": "B班",
			"2020-04-05": 1,
			"2020-04-04": 0
		},
		{
			"2020-04-01": 1,
			"2020-04-03": 1,
			"2020-04-02": 0,
			"sch_name": "N班",
			"2020-04-05": 1,
			"2020-04-04": 0
		},
		{
			"2020-04-01": 0,
			"2020-04-03": 0,
			"2020-04-02": 2,
			"sch_name": "P班",
			"2020-04-05": 0,
			"2020-04-04": 1
		},
		{
			"2020-04-01": 1,
			"2020-04-03": 0,
			"2020-04-02": 0,
			"sch_name": "休息",
			"2020-04-05": 0,
			"2020-04-04": 0
		},
		{
			"2020-04-01": 0,
			"2020-04-03": 0,
			"2020-04-02": 0,
			"sch_name": "测试班次1",
			"2020-04-05": 0,
			"2020-04-04": 0
		},
		{
			"2020-04-01": 0,
			"2020-04-03": 0,
			"2020-04-02": 0,
			"sch_name": "测试班次2",
			"2020-04-05": 0,
			"2020-04-04": 0
		},
		{
			"2020-04-01": 0,
			"2020-04-03": 0,
			"2020-04-02": 0,
			"sch_name": "行政",
			"2020-04-05": 0,
			"2020-04-04": 0
		}
	]
}