MySQL中求每个学生最高科目的分数

首先，感谢江涛小哥哥，出了这个需求

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'dd', '11');
INSERT INTO `student` VALUES ('2', 'ww', '13');
INSERT INTO `student` VALUES ('3', 'qq', '15');
INSERT INTO `student` VALUES ('4', 'ee', '11');
INSERT INTO `student` VALUES ('5', 'dd', '11');

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '语文');
INSERT INTO `course` VALUES ('2', '数学');
INSERT INTO `course` VALUES ('3', '英语');

CREATE TABLE `result` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `SID` int(11) DEFAULT NULL,
  `CID` int(11) DEFAULT NULL,
  `SCORE` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of result
-- ----------------------------
INSERT INTO `result` VALUES ('1', '1', '2', '30');
INSERT INTO `result` VALUES ('2', '1', '1', '50');
INSERT INTO `result` VALUES ('3', '1', '3', '79');
INSERT INTO `result` VALUES ('4', '2', '1', '33');
INSERT INTO `result` VALUES ('5', '2', '2', '55');
INSERT INTO `result` VALUES ('6', '2', '3', '66');
INSERT INTO `result` VALUES ('7', '3', '1', '111');
INSERT INTO `result` VALUES ('8', '3', '3', '33');
INSERT INTO `result` VALUES ('9', '3', '2', '11');
INSERT INTO `result` VALUES ('10', '3', '2', '135');

SELECT
	cc.id,
	student. NAME,
	course. NAME
FROM
	(
		SELECT
			s1.id,
			s1.SID,
			s1.CID,
			s1.SCORE
		FROM
			result s1
		JOIN (
			SELECT
				SID,
				MAX(r.SCORE) sumscore
			FROM
				result r
			GROUP BY
				r.SID
		) AS s2 ON s1.SID = s2.SID
		AND s1.SCORE = s2.sumscore
		ORDER BY
			SID
	) AS cc,
	student,
	course
WHERE
	cc.SID = student.id
AND cc.CID = course.id