mybatis-MySQL实现动态行转列

1.建立课程表

DROP TABLE IF EXISTS `curriculum`;
CREATE TABLE `curriculum` (
  `courseno` varchar(20) NOT NULL,
  `coursenm` varchar(100) NOT NULL,
  PRIMARY KEY (`courseno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';

INSERT INTO `curriculum` VALUES ('C001', '大学语文');
INSERT INTO `curriculum` VALUES ('C002', '新视野英语');
INSERT INTO `curriculum` VALUES ('C003', '离散数学');
INSERT INTO `curriculum` VALUES ('C004', '概率论与数理统计');
INSERT INTO `curriculum` VALUES ('C005', '线性代数');
INSERT INTO `curriculum` VALUES ('C006', '高等数学(一)');
INSERT INTO `curriculum` VALUES ('C007', '高等数学(二)');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q3HMm4Wk-1640772032303)(C:\Users\张小辰\AppData\Roaming\Typora\typora-user-images\image-20211229174912643.png)]

二，建立成绩表

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `stuid` varchar(16) NOT NULL,
  `courseno` varchar(20) NOT NULL,
  `scores` float DEFAULT NULL,
  PRIMARY KEY (`stuid`,`courseno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `score` VALUES ('1001', 'C001', '67');
INSERT INTO `score` VALUES ('1001', 'C002', '87');
INSERT INTO `score` VALUES ('1001', 'C003', '83');
INSERT INTO `score` VALUES ('1001', 'C004', '88');
INSERT INTO `score` VALUES ('1001', 'C005', '77');
INSERT INTO `score` VALUES ('1001', 'C006', '77');
INSERT INTO `score` VALUES ('1002', 'C001', '68');
INSERT INTO `score` VALUES ('1002', 'C002', '88');
INSERT INTO `score` VALUES ('1002', 'C003', '84');
INSERT INTO `score` VALUES ('1002', 'C004', '89');
INSERT INTO `score` VALUES ('1002', 'C005', '78');
INSERT INTO `score` VALUES ('1002', 'C006', '78');
INSERT INTO `score` VALUES ('1003', 'C001', '69');
INSERT INTO `score` VALUES ('1003', 'C002', '89');
INSERT INTO `score` VALUES ('1003', 'C003', '85');
INSERT INTO `score` VALUES ('1003', 'C004', '90');
INSERT INTO `score` VALUES ('1003', 'C005', '79');
INSERT INTO `score` VALUES ('1003', 'C006', '79');
INSERT INTO `score` VALUES ('1004', 'C001', '70');
INSERT INTO `score` VALUES ('1004', 'C002', '90');
INSERT INTO `score` VALUES ('1004', 'C003', '86');
INSERT INTO `score` VALUES ('1004', 'C004', '91');
INSERT INTO `score` VALUES ('1004', 'C005', '80');
INSERT INTO `score` VALUES ('1004', 'C006', '80');
INSERT INTO `score` VALUES ('1005', 'C001', '71');
INSERT INTO `score` VALUES ('1005', 'C002', '91');
INSERT INTO `score` VALUES ('1005', 'C003', '87');
INSERT INTO `score` VALUES ('1005', 'C004', '92');
INSERT INTO `score` VALUES ('1005', 'C005', '81');
INSERT INTO `score` VALUES ('1005', 'C006', '81');
INSERT INTO `score` VALUES ('1006', 'C001', '72');
INSERT INTO `score` VALUES ('1006', 'C002', '92');
INSERT INTO `score` VALUES ('1006', 'C003', '88');
INSERT INTO `score` VALUES ('1006', 'C004', '93');
INSERT INTO `score` VALUES ('1006', 'C005', '82');
INSERT INTO `score` VALUES ('1006', 'C006', '82');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fzb65SJh-1640772032305)(C:\Users\张小辰\AppData\Roaming\Typora\typora-user-images\image-20211229175021636.png)]

三，建立学生表

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `stuid` varchar(16) NOT NULL COMMENT '学号',
  `stunm` varchar(20) NOT NULL COMMENT '学生姓名',
  PRIMARY KEY (`stuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('1001', '张三');
INSERT INTO `student` VALUES ('1002', '李四');
INSERT INTO `student` VALUES ('1003', '赵二');
INSERT INTO `student` VALUES ('1004', '王五');
INSERT INTO `student` VALUES ('1005', '刘青');
INSERT INTO `student` VALUES ('1006', '周明');

四，java利用mybatis实现动态行列

select distinct courseno from curriculum //获取所有课程编号

五，拼装SQL语句

        StringBuilder sb = new StringBuilder();
        for (CurriculumVO vo :vos) {
            sb.append("MAX( CASE courseno WHEN '" + vo.getCourseno());
            sb.append("' THEN scores ELSE 0 END ) AS " + vo.getCourseno()+",");
        }

		//消除最后一个逗号
        String sql = sb.subSequence(0, sb.length() - 1).toString();

六，在mybatis中对SQL语句进行封装

    <select id="getResult" resultType="java.util.Map">
        select stuid,scores,${sql} from score group by stuid
    </select>
        
        //mapper层返回对象
        List<Map<String,Object>> getResult(@Param("sql") String sql);

		//
        List<Map<String, Object>> result = scoreDAO.getResult(sql);

getResult(@Param(“sql”) String sql);

	//
    List<Map<String, Object>> result = scoreDAO.getResult(sql);