mysql行转列

DROP TABLE IF EXISTS `class_info`;
CREATE TABLE `class_info` (
  `student_name` varchar(50) NOT NULL,
  `class_name` varchar(50) NOT NULL,
  `score` double(30,0) DEFAULT NULL,
  PRIMARY KEY (`student_name`,`class_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class_info
-- ----------------------------
INSERT INTO `class_info` VALUES ('李娜', 'java', '100');
INSERT INTO `class_info` VALUES ('李娜', 'mysql', '100');
INSERT INTO `class_info` VALUES ('随朗', 'java', '100');
INSERT INTO `class_info` VALUES ('随朗', 'mysql', '90');
INSERT INTO `class_info` VALUES ('随朗', '语文', '100');

表结构和数据如上:

目表是如图：图一 转换成 图二

图一

图二

SELECT
	student_name,
	MAX(
		CASE class_name
		WHEN 'java' THEN
			score
		END
	) java,
	MAX(
		CASE class_name
		WHEN 'mysql' THEN
			score
		END
	) mysql,
	MAX(
		CASE class_name
		WHEN '语文' THEN
			score
		END
	) 语文
FROM
	class_info
GROUP BY
	student_name

下图，语文有为null的

解决方案：

SELECT
	student_name,
	MAX(
		CASE class_name
		WHEN 'java' THEN
			score
		END
	) java,
	MAX(
		CASE class_name
		WHEN 'mysql' THEN
			score
		END
	) mysql,
	MAX(
		CASE class_name
		WHEN '语文' THEN
			score
		ELSE
				0
		END
	) 语文
FROM
	class_info
GROUP BY
	student_name