学生成绩表 :
CREATE TABLE `tabscore` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stuId` int(11) DEFAULT NULL,
`stuName` varchar(255) DEFAULT NULL,
`course` varchar(255) DEFAULT NULL,
`score` float(3,1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `tabscore` VALUES ('1', '1', '张三', '语文', '85.0');
INSERT INTO `tabscore` VALUES ('2', '1', '张三', '数学', '89.0');
INSERT INTO `tabscore` VALUES ('3', '2', '李四', '语文', '85.0');
INSERT INTO `tabscore` VALUES ('4', '2', '李四', '数学', '80.0');
INSERT INTO `tabscore` VALUES ('5', '3', '王五', '语文', '85.0');
INSERT INTO `tabscore` VALUES ('6', '3', '王五', '数学', '99.0');
要求查询每科目前两名学生及成绩
- 最先想到的方法,通过子查询得到每课程的前两名
select tabscore.* from tabscore
where score in
(select score from tabscore tb2 where tb2.course = tabscore.course ORDER BY tb2.score desc LIMIT 0,2)
mysql却报了个错:This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
意思是limit不能用于IN/ALL/ANY/SOME 子查询,这个问题可以通过再包装一个子查询解决,但这里用不了,因为子查询需要父查询中的数据作为查询条件
- 方案2,这个方法很难想到
select tabscore.* from tabscore
where
(select count(1) from tabscore tb2 where tb2.course = tabscore.course and tb2.score>tabscore.score)<2