mysql 行列转换，行转列和列转行

目录

行转列

列转行

---

行转列

建立测试表，添加测试数据

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `subject` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `stu_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('语文', '88', '101');
INSERT INTO `score` VALUES ('语文', '90', '2');
INSERT INTO `score` VALUES ('语文', '78', '3');
INSERT INTO `score` VALUES ('数学', '93', '3');
INSERT INTO `score` VALUES ('数学', '89', '101');
INSERT INTO `score` VALUES ('数学', '98', '2');
INSERT INTO `score` VALUES ('英语', '99', '2');
INSERT INTO `score` VALUES ('英语', '69', '3');
INSERT INTO `score` VALUES ('英语', '96', '101');
INSERT INTO `score` VALUES ('英语', '99', '4');

表数据结构如图：

效果图：

1、使用case when

SELECT stu_id,
sum(case when subject='语文' then score else 0 end) as '语文',
sum(case when subject='数学' then score else 0 end) as '数学',
sum(case when subject='英语' then score else 0 end) as '英语'
 FROM score GROUP BY stu_id;

2、使用if

SELECT stu_id,
sum(if(subject='语文',score,0 )) as '语文',
sum(if(subject='数学',score,0 )) as '数学',
sum(if(subject='英语',score,0 )) as '英语'
 FROM score GROUP BY stu_id;

列转行

建立测试表，添加测试数据

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `stu_id` int(11) DEFAULT NULL,
  `chinese_score` int(11) DEFAULT NULL,
  `math_score` int(11) DEFAULT NULL,
  `english_score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('101', '80', '90', '100');
INSERT INTO `score` VALUES ('102', '88', '78', '99');
INSERT INTO `score` VALUES ('103', '69', '98', '87');

表数据结构如图：

列转行sql：

SELECT stu_id,'语文' AS course,chinese_score AS score FROM score
UNION ALL
SELECT stu_id,'数学' AS course,math_score AS score FROM score
UNION ALL
SELECT stu_id,'外语' AS course,english_score AS score FROM score
ORDER BY stu_id

转换效果：