DROP TABLE IF EXISTS students; CREATE TABLE students( `id` INT ( 3 ) NOT NULL COMMENT '主键' , `name` VARCHAR ( 10 ) NOT NULL COMMENT '姓名' , `age` INT ( 3 ) NOT NULL COMMENT '年龄' , `class` VARCHAR ( 10 ) NOT NULL COMMENT '班级' , PRIMARY KEY (`id`) # 成绩表 DROP TABLE IF EXISTS grade; CREATE TABLE grade( `id` INT ( 3 ) NOT NULL COMMENT '主键' , `student_id` INT ( 3 ) NOT NULL COMMENT '学生id' , `subject` VARCHAR ( 20 ) NOT NULL COMMENT '科目' , `grade` INT ( 3 ) NOT NULL COMMENT '成绩' , PRIMARY KEY (`id`)

# 学生表
INSERT INTO students VALUES(1, '詹溪', 18, '高三（2）班');
INSERT INTO students VALUES(2, '徐柯', 18, '高三（3）班');
INSERT INTO students VALUES(3, '蓝毅', 17, '高三（3）班');
INSERT INTO students VALUES(4, '温邱', 18, '高三（3）班');
# 成绩表
INSERT INTO grade VALUES(1, 1, "math", 98);
INSERT INTO grade VALUES(2, 1, "chinese", 72);
INSERT INTO grade VALUES(3, 1, "english", 100);
INSERT INTO grade VALUES(4, 2, "math", 92);
INSERT INTO grade VALUES(5, 2, "chinese", 95);
INSERT INTO grade VALUES(6, 2, "english", 91);
INSERT INTO grade VALUES(7, 3, "math", 86);
INSERT INTO grade VALUES(8, 3, "chinese", 80);
INSERT INTO grade VALUES(9, 3, "english", 91);
INSERT INTO grade VALUES(10, 4, "math", 95);
INSERT INTO grade VALUES(11, 4, "chinese", 92);
INSERT INTO grade VALUES(12, 4, "english", 93);
普通联合查询
SELECT s.name, s.class, g.subject, g.grade FROM students s
LEFT JOIN grade g ON s.id = g.student_id
现在需要将每个人的成绩整合成一行的多列展示。

固定列就不再赘述了，使用case...when...then或者if判断科目即可。

但如果科目不确定，或者是科目太多不想一行行写，就需要使用动态拼接的方式，生成语句。

动态行转列
# 将case...when...then或者if语句动态拼接起来。
SELECT
    @sequence :=CONCAT(@sequence,'SUM(IF(subject= "',subject,'",grade,0)) as ',subject, ',') AS sequence
    (SELECT DISTINCT subject FROM grade) g,
    (SELECT @sequence:="") s;
# 与固定列拼接成查询语句
SET @sql = CONCAT('SELECT s.name, s.class,',@sequence,' SUM(grade) as TOTAL FROM students s LEFT JOIN grade g ON s.id = g.student_id GROUP BY s.id');
# 执行sql
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
到此为止，成功使用sql查出想要的结果。

但是mybatis好像没有执行语句的标签。

只有select、insert、update等。

那么我们还是可以使用拼接语句，再塞到mybatis映射文件中去使用select标签执行。

实体没有动态字段的属性怎么办？那就使用Map类型接收结果。
使用mybatis实行动态行转列查询
Mapper.xml
    <select id="getSequence" resultType="java.lang.String">
        SELECT
            @sequence :=CONCAT(@sequence,'SUM(IF(subject= "',subject,'",grade,0)) as ',subject, ',') AS sequence
            (SELECT DISTINCT subject FROM grade) g,
            (SELECT @sequence:="") s;
    </select>
    <select id="findList" parameterType="java.lang.String" resultType="java.util.Map">
        ${sql}
    </select>
Dao.java
    public List<String> getSequence();
    public List<Map<String, Object>> findList(String sql);
Service.java
    public List<Map<String, Object>> findList() {
        //获取动态拼接sql
        List<String> dynamicSequence = dao.getSequence();
        //手动与固定列拼接
        String sql = "SELECT s.name, s.class," + 
                    dynamicSequence.get(dynamicSequence.size() - 1) +
                    " SUM(grade) as TOTAL" + 
                    " FROM students s" + 
                    " LEFT JOIN grade g ON s.id = g.student_id" + 
                    " GROUP BY s.id";
        //查询结果
        List<Map<String, Object>> mapList = dao.findList(sql);
        return mapList;
自此，使用mybatis实现查询行转动态列。
如果前端使用layui，可以参考我的另一篇文章layui-table动态列实现
参考资料：

mysql 行转列 列转行

mybatis 实现自定义sql
  
 
     
 
 

    Mervyn
        
 关注  
私信