CREATE TABLE `student` (
`id` int(11) NOT NULL,
`sub` varchar(255) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
`userid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '语文', '98', '1');
INSERT INTO `student` VALUES ('2', '数学', '99', '1');
INSERT INTO `student` VALUES ('3', '英语', '66', '1');
INSERT INTO `student` VALUES ('4', '语文', '198', '11');
INSERT INTO `student` VALUES ('5', '数学', '991', '11');
INSERT INTO `student` VALUES ('6', '英语', '661', '11');
select userid,
max(case sub when "语文" then score else 0 end) as "语文",
max(case sub when "数学" then score else 0 end) as "数学",
max(case sub when "英语" then score else 0 end) as "英语"
from student GROUP BY userid;
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`english` int(11) DEFAULT NULL,
`math` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '98', '89');
INSERT INTO `student` VALUES ('2', '99', '99');
INSERT INTO `student` VALUES ('3', '66', '99');
select * from (
select id,"英语" as subject,english as score from student
UNION
select id,"数学" as subject,math as score from student
) tb ORDER BY id;
https://blog.csdn.net/yuxin6866/article/details/53107286