SQL行转列案例

 

• 表结构

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `subject` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` float(255, 0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

• 原始数据

• 行转列后

• 转换的SQL

SELECT
	s.username AS '姓名',
	max( CASE s.SUBJECT WHEN '语文' THEN s.score ELSE 0 END ) AS '语文',
	max( CASE s.SUBJECT WHEN '数学' THEN s.score ELSE 0 END ) AS '数学',
	max( CASE s.SUBJECT WHEN '英语' THEN s.score ELSE 0 END ) AS '英语',
	max( CASE s.SUBJECT WHEN '生物' THEN s.score ELSE 0 END ) AS '生物',
	max( CASE s.SUBJECT WHEN '化学' THEN s.score ELSE 0 END ) AS '化学' 
FROM
	student s 
GROUP BY
	s.username