MySql 行转列操作

行转列操作

将行数据转为列数据展示

话不多说 开干

• 准备数据

CREATE TABLE `t_score`  (
  `id` int(11) NULL DEFAULT NULL,
  `account` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `grade` int(255) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;


INSERT INTO `t_score` VALUES (1, 'match', 85);
INSERT INTO `t_score` VALUES (1, 'chinese', 80);
INSERT INTO `t_score` VALUES (1, 'english', 99);
INSERT INTO `t_score` VALUES (2, 'match', 90);
INSERT INTO `t_score` VALUES (2, 'chinese', 80);
INSERT INTO `t_score` VALUES (1, 'english', 90);

• 查看数据
  

• 行转列查询

select id , 
		MAX(case account when 'match' then grade else 0 end)  as `match`,
		MAX(case account when 'chinese' then grade else 0 end)  as `chinese`,
		MAX(case account when 'english' then grade else 0 end)  as `english` 
FROM 
	t_score
group by 
	id