MySQL 不支持pivot和unpivot

既然MySQL不支持pivot怎么实现列转行呢？

一、我们抽象一下业务 假设我们有2个班级，每个班级里都有同学，如果我想只展示一个班级的所有学生，在一条记录里展示，那么就会用到列转行

首先我们创建一下表

CREATE TABLE `student` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `class_id` VARCHAR(255) DEFAULT NULL,
  `student_id` VARCHAR(255) DEFAULT NULL,
  `student_name` VARCHAR(255) DEFAULT NULL,
  `student_type` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

插入一下相关数据

INSERT INTO student_info (class_id, student_id, student_name, student_type  ) VALUES ('1', '1', '小明','1');
INSERT INTO student_info (class_id, student_id, student_name, student_type  ) VALUES ('1', '2', '小花','2');
INSERT INTO student_info (class_id, student_id, student_name, student_type ) VALUES ('1', '4', '小英', '1');
INSERT INTO student_info (class_id, student_id, student_name, student_type  ) VALUES ('2', '1', '二明','1');

让我们查询一下全部的数据 SELECT * FROM student_info;

只查询一班的数据的话 SELECT * FROM student_info where class_id = '1';

既然MySQL不支持pivot ，那怎么解决呢？

用case when 查询，但是case when 查询后还是多条，怎么处理呢？

SELECT class_id,
    CASE WHEN student_type = '1' THEN student_name END `优秀学生`,
    CASE WHEN student_type = '2' THEN student_name END `一般学生`,
    CASE WHEN student_type = '3' THEN student_name END `重点关注学生`
    FROM student_info WHERE class_id = '1';

用到了max函数可以解决null 的问题

SELECT     class_id `班级编号`,
    MAX(CASE WHEN student_type = '1' THEN student_name END) `优秀学生`,
    MAX(CASE WHEN student_type = '2' THEN student_name END) `一般学生`,
    MAX(CASE WHEN student_type = '3' THEN student_name END) `重点关注学生`
    FROM student_info WHERE class_id = '1';

那现在出现了一个引申，如果学生类型有重复的怎么办？

INSERT INTO student_info (class_id, student_id, student_name, student_type ) VALUES ('1', '4', '小英', '1');

再查询一次发现，完蛋了，把小明的数据给覆盖了，怎么办呢？

只能将相同类型的数据并列展示了，用到了GROUP_CONCAT函数

SELECT a.class_id `班级编号`,GROUP_CONCAT(a.yxxs) `优秀学生`,GROUP_CONCAT(a.ybxs) `一般学生`,GROUP_CONCAT(a.zdgzxs) `重点关注学生` FROM    
(SELECT class_id,
    CASE WHEN student_type = '1' THEN student_name END `yxxs`,
    CASE WHEN student_type = '2' THEN student_name END `ybxs`,
    CASE WHEN student_type = '3' THEN student_name END `zdgzxs`
    FROM student_info WHERE class_id = '1') a;

这次查询出的数据展示

既解决了行转列，又解决了行转列时重复的问题

以上就是列转行的相关sql实现

二、行转列

同样的，先抽象一下业务（借用一下上边的截图），假设一个班级里只有三个学生的时候，这三个学生恰好分布在优秀学生、一般学生、重点学生各一人

怎么展示成 班级编号、学生类型（1,2,3）、学生姓名呢？

先创建表

CREATE TABLE `student_info_2` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `class_id` VARCHAR(255) DEFAULT NULL,
  `yxxs` VARCHAR(255) DEFAULT NULL,
  `ybxs` VARCHAR(255) DEFAULT NULL,
  `zdgzxs` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
--新增数据
INSERT INTO student_info_2 (class_id, yxxs, ybxs, zdgzxs  ) VALUES ('1', '小明', '小花','小文');

查询数据可以看到 SELECT * FROM student_info_2;

SELECT class_id, '1' student_type, yxxs student_name FROM student_info_2 WHERE class_id = '1'
UNION 
SELECT class_id, '2' student_type, ybxs student_name FROM student_info_2 WHERE class_id = '1'
UNION
SELECT class_id, '3' student_type, zdgzxs student_name FROM student_info_2 WHERE class_id = '1';

使用union可以实现，查询结果为

那如果优秀学生有两个人，甚至是多个人呢？

UPDATE student_info_2 SET yxxs = '小明,小英';

重新查询数据可以看到

这样的话用上边的sql查询就只能转换成以下结果了

sql又需要优化了，不然展示又有问题了

SELECT 
a.class_id, a.student_type,
SUBSTRING_INDEX(
    SUBSTRING_INDEX(
        a.student_name,
        ',',
        b.`help_topic_id` + 1
    ),
    ',', -1
) student_name
FROM
(SELECT class_id, '1' student_type, yxxs student_name FROM student_info_2 WHERE class_id = '1'
UNION 
SELECT class_id, '2' student_type, ybxs student_name FROM student_info_2 WHERE class_id = '1'
UNION
SELECT class_id, '3' student_type, zdgzxs student_name FROM student_info_2 WHERE class_id = '1') a
JOIN mysql.`help_topic` b ON b.help_topic_id < (
    LENGTH(a.student_name) - LENGTH(
        REPLACE(a.student_name, ',' ,'')
    ) + 1
);

这次的sql优化用到了mysql.`help_topic` 和SUBSTRING_INDEX 函数，查询出的结果展示是

以上就是行转列的相关sql实现

谢谢查看