竖表变横表
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');
横表变竖表
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`chinese` int(11) DEFAULT 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,chinese as score from student
UNION
select id,"英语" as subject,english as score from student
UNION
select id,"数学" as subject,math as score from student
) tb ORDER BY id;