遇到的一个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
