MySQL横竖表转换

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;