Mysql行转列

遇到的一个Sql面试题：

image.png

建表：

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `name` varchar(64) DEFAULT NULL,
  `course` varchar(32) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('张青', '语文', 72);
INSERT INTO `student` VALUES ('王凤', '数学', 72);
INSERT INTO `student` VALUES ('张华', '英语', 81);
INSERT INTO `student` VALUES ('张青', '物理', 67);
INSERT INTO `student` VALUES ('李立', '化学', 98);
INSERT INTO `student` VALUES ('张燕', '物理', 75);
INSERT INTO `student` VALUES ('张青', '化学', 76);
INSERT INTO `student` VALUES ('张华', '数学', 80);
INSERT INTO `student` VALUES ('张燕', '语文', 90);
COMMIT;

Sql：

SELECT 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 ) '英语',
    max( CASE course WHEN '化学' THEN score ELSE 0 END ) '化学',
    max( CASE course WHEN '物理' THEN score ELSE 0 END ) '物理',
    avg(score) avg
FROM
    student 
WHERE
    NAME IN ( SELECT NAME FROM student GROUP BY NAME HAVING avg( score ) > 75 ) 
GROUP BY
NAME

结果：

image.png