MySQL获取学生各科前三的成绩

创建科目成绩表：

DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores` (
  `name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `subject` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `scores` VALUES ('学生a', 'java', '100');
INSERT INTO `scores` VALUES ('学生b', 'java', '90');
INSERT INTO `scores` VALUES ('学生c', 'java', '90');
INSERT INTO `scores` VALUES ('学生d', 'java', '60');
INSERT INTO `scores` VALUES ('学生e', 'java', '80');
INSERT INTO `scores` VALUES ('学生a', 'python', '100');
INSERT INTO `scores` VALUES ('学生b', 'python', '90');
INSERT INTO `scores` VALUES ('学生c', 'python', '90');
INSERT INTO `scores` VALUES ('学生d', 'python', '60');
INSERT INTO `scores` VALUES ('学生e', 'python', '80');

查询前三各科成绩：

SELECT s1.name,s1.subject,s1.score FROM scores s1 
WHERE(SELECT count(1)FROM scores s2 
WHERE s1.subject = s2.subject AND s2.score >= s1.score) <= 3 
ORDER BY s1.subject, s1.score DESC