常见的行列转换包括以下四种情况:
1.列转行
2.行转列
3.列转换成字符串
4.字符串转换成列
1.列转行
导入数据
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
`id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
`name` varchar(50) DEFAULT NULL COMMENT '姓名',
`course` varchar(50) DEFAULT NULL COMMENT '课程',
`score` int(3) DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
INSERT INTO `t_student` VALUES (1,'张三', '语文', 95);
INSERT INTO `t_student` VALUES (2,'李四', '语文', 99);
INSERT INTO `t_student` VALUES (3,'王五', '语文', 80);
INSERT INTO `t_student` VALUES (4,'张三', '数学', 86);
INSERT INTO `t_student` VALUES (5,'李四', '数学', 96);
INSERT INTO `t_student` VALUES (6,'王五', '数学', 81);
INSERT INTO `t_student` VALUES (7,'张三', '英语', 78);
INSERT INTO `t_student` VALUES (8,'李四', '英语', 88);
INSERT INTO `t_student` VALUES (9,'王五', '英语', 87);
INSERT INTO `t_student` VALUES (10,'张三', '历史', 98);
INSERT INTO `t_student` VALUES (11,'李四', '历史', 85);
INSERT INTO `t_student` VALUES (12,'王五', '历史', 89);
t_student表 (学生成绩表)
1.1MAX(CASE WEHN)方法
SELECT name as '姓名',
MAX(CASE WHEN course = '语文' THEN score END) AS '语文',
MAX(CASE WHEN course = '数学' THEN score END) AS '数学&#