mysql列转行操作

1、建表

CREATE TABLE TEST_TB_GRADE (
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=1 DEFAULT CHARSET=utf8;

2.插入数据

insert into TEST_TB_GRADE(USER_NAME, COURSE, SCORE) values
(“张三”, “数学”, 34),
(“张三”, “语文”, 58),
(“张三”, “英语”, 58),
(“李四”, “数学”, 45),
(“李四”, “语文”, 87),
(“李四”, “英语”, 45),
(“王五”, “数学”, 76),
(“王五”, “语文”, 34),
(“王五”, “英语”, 89);

3.需求结果是这样，对课程列进行转行操作，实现结果如图。
SQL语句如下：
SELECT
user_name,
MAX(CASE course WHEN ‘语文’ THEN score ELSE 0 END) AS ‘语文’,
MAX(CASE course WHEN ‘数学’ THEN score ELSE 0 END) AS ‘数学’,
MAX(CASE course WHEN '英语’THEN score ELSE 0 END) AS ‘英语’
FROM TEST_TB_GRADE
GROUP BY user_name;

这里group By 主要是用来对user_name进行分组去重。