MySQL行转列

• 创建表

CREATE TABLE `student` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(20) DEFAULT NULL,
  `COURSE` varchar(20) DEFAULT NULL,
  `SCORE` float DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

• 添加数据

INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (10, '张三', '数学', 34);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (11, '张三', '语文', 58);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (12, '张三', '英语', 58);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (13, '李四', '数学', 45);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (14, '李四', '语文', 87);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (15, '李四', '英语', 45);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (16, '王五', '数学', 76);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (17, '王五', '语文', 34);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (18, '王五', '英语', 89);

• 实现

  SELECT
    user_name,
    max( CASE course WHEN "数学" THEN score ELSE 0 END ) 数学,
    max( CASE course WHEN "语文" THEN score ELSE 0 END ) 语文,
    max( CASE course WHEN "英语" THEN score ELSE 0 END ) 英语 
FROM
    student 
GROUP BY
    user_name;

SELECT DISTINCT
    t.user_name,
    ( SELECT a.score FROM student a WHERE t.USER_NAME = a.USER_NAME AND a.COURSE = "数学" ) 数学,
    ( SELECT a.score FROM student a WHERE t.USER_NAME = a.USER_NAME AND a.COURSE = "语文" ) 语文,
    ( SELECT a.score FROM student a WHERE t.USER_NAME = a.USER_NAME AND a.COURSE = "英语" ) 英语 
FROM
    student t