MySQL行列互换的原理及具体实现

1 行转列

实现原理：使用类似于Case When或者If等判断条件，当满足条件的时候，我们就把它当做新的一列。

1.1 表

CREATE TABLE `test1` (
  `name` varchar(255) DEFAULT NULL,
  `course` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test1` VALUES ('张三', 'chinese', 80);
INSERT INTO `test1` VALUES ('张三', 'math', 85);
INSERT INTO `test1` VALUES ('张三', 'english', 90);
INSERT INTO `test1` VALUES ('李四', 'chinese', 90);
INSERT INTO `test1` VALUES ('李四', 'math', 85);
INSERT INTO `test1` VALUES ('李四', 'english', 80);

1.2 方法1 -> 使用group_concat函数

select name,
	GROUP_CONCAT(case WHEN course='chinese' then score end SEPARATOR '') 'chinese',
	GROUP_CONCAT(case WHEN course='math' then score end SEPARATOR '') 'math',
	GROUP_CONCAT(case WHEN course='english' then score end SEPARATOR '') 'english'
from test1
GROUP BY name;

1.3 方法2 -> 使用聚合函数

select name,
	MAX(case WHEN course='chinese' then score end) as chinese,
	MAX(case WHEN course='math' then score else 0 end) as math,
	MAX(case WHEN course='english' then score end) as english
from test1
GROUP BY name;

1.4 方法3 -> 使用distinct

select DISTINCT c.name as name,
	(select score from test1 where name = c.name and course='chinese' ) as chinese,
	(select score from test1 where name = c.name and course='math' ) as math,
	(select score from test1 where name = c.name and course='english' ) as english
from test1 c;

2 列转行

实现原理：采用Union或者Union all的形式，把多个结果集合并起来即可。

2.1 表

CREATE TABLE `test2` (
  `name` varchar(255) DEFAULT NULL,
  `chinese` int(11) DEFAULT NULL,
  `math` int(11) DEFAULT NULL,
  `english` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test2` VALUES ('张三', 80, 85, 90);
INSERT INTO `test2` VALUES ('李四', 90, 85, 80);

2.2 方法1 -> group by + union

select name, 'chinese' as course, chinese as score from test2 GROUP BY name,chinese
union 
select name, 'math' as course, math as score from test2 GROUP BY name,math
union 
select name, 'english' as course, english as score from test2 GROUP BY name,english

2.3 方法2 -> distinct + union

select DISTINCT name, 'chinese' as course, chinese as score from test2
union 
select DISTINCT name, 'math' as course, math as score from test2
union 
select DISTINCT name, 'english' as course, english as score from test2

3 总结

由于最近工作中遇到了类似的问题，通过MySQL的行列互换实现了业务的需求，所以特意总结了下，希望对大家也有所帮助，如果你有更好的实现方式也可以通过留言的形式补充进来，感谢支持...