sql列转行
建表语句:
SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for studentscores -- ---------------------------- DROP TABLE IF EXISTS `studentscores`; CREATE TABLE `studentscores` ( `UserName` varchar(20) CHARACTER SET utf8 DEFAULT NULL, `Subject` varchar(30) CHARACTER SET utf8 DEFAULT NULL, `Score` float DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of studentscores -- ---------------------------- INSERT INTO `studentscores` VALUES ('Nick', '语文', '80'); INSERT INTO `studentscores` VALUES ('Nick', '数学', '90'); INSERT INTO `studentscores` VALUES ('Nick', '英语', '70'); INSERT INTO `studentscores` VALUES ('Nick', '生物', '85'); INSERT INTO `studentscores` VALUES ('Kent', '语文', '80'); INSERT INTO `studentscores` VALUES ('Kent', '数学', '90'); INSERT INTO `studentscores` VALUES ('Kent', '英语', '70'); INSERT INTO `studentscores` VALUES ('Kent', '生物', '85');
表数据:
列转行:
select username, max(case subject when '语文' then score else 0 end) as '语文', max(case subject when '数学' then score else 0 end) as '数学', max(case subject when '英语' then score else 0 end) as '英语', max(case subject when '生物' then score else 0 end) as '生物' from studentscores group by username
结果: