SQL利用Case When Then用法、行转列

1. 目标

完成如下图的转换

2. Case When Then行转列

1. 环境：mysql
2. 数据准备
   创建user_info与user_score表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user_info
-- ----------------------------
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info`  (
  `id` bigint(20) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_info
-- ----------------------------
INSERT INTO `user_info` VALUES (1, '小明', '男');
INSERT INTO `user_info` VALUES (2, '小红', '女');
INSERT INTO `user_info` VALUES (3, '小哈', '女');

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user_score
-- ----------------------------
DROP TABLE IF EXISTS `user_score`;
CREATE TABLE `user_score`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `subject` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` float NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_score
-- ----------------------------
INSERT INTO `user_score` VALUES (1, '1', '语文', 80);
INSERT INTO `user_score` VALUES (2, '1', '数学', 90);
INSERT INTO `user_score` VALUES (3, '1', '英语', 70);
INSERT INTO `user_score` VALUES (4, '1', '生物', 85);
INSERT INTO `user_score` VALUES (5, '2', '语文', 80);
INSERT INTO `user_score` VALUES (6, '2', '数学', 92);
INSERT INTO `user_score` VALUES (7, '2', '英语', 76);
INSERT INTO `user_score` VALUES (8, '2', '生物', 88);
INSERT INTO `user_score` VALUES (9, '3', '语文', 60);
INSERT INTO `user_score` VALUES (10, '3', '数学', 82);
INSERT INTO `user_score` VALUES (11, '3', '英语', 96);
INSERT INTO `user_score` VALUES (12, '3', '生物', 78);

SET FOREIGN_KEY_CHECKS = 1;

3. 需要转换的数据如下

select user_info.*,user_score.subject,user_score.score
from user_info LEFT JOIN user_score on user_score.user_id=user_info.id
order by id asc,user_score.subject desc

4. 利用case when then将上述数据进行行转列

select user_info.*,
max(case when user_score.subject='语文' then score else 0 end) as chinese,
max(case when user_score.subject='英语' then score else 0 end) as english,
max(case when user_score.subject='生物' then score else 0 end) as biology,
max(case when user_score.subject='数学' then score else 0 end) as math
from user_info LEFT JOIN user_score on user_score.user_id=user_info.id
group by user_info.name
order by id

参考博客：

1. case when then 讲解：https://blog.csdn.net/weixin_42402688/article/details/81035175
2. 行列互换：http://www.guodaxiong.com/150.html#i-5