竖表变横表
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;
