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 test1GROUP 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 englishfrom test1GROUP 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 englishfrom 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,chineseunion select name, 'math' as course, math as score from test2 GROUP BY name,mathunion 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 test2union select DISTINCT name, 'math' as course, math as score from test2union select DISTINCT name, 'english' as course, english as score from test2

3 总结

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

【温馨提示】

点赞+收藏文章，关注我并私信回复【面试题解析】，即可100%免费领取楼主的所有面试题资料！