查询每科成绩前3和第3的学生

1、sql文件：

DROP TABLE IF EXISTS `studentscore`;
CREATE TABLE `studentscore` (
  `scoreid` int(11) NOT NULL AUTO_INCREMENT,
  `sourceid` int(11) DEFAULT NULL,
  `userid` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`scoreid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

INSERT INTO `studentscore` VALUES ('1', '1', '1', '60');
INSERT INTO `studentscore` VALUES ('2', '2', '1', '80');
INSERT INTO `studentscore` VALUES ('3', '3', '1', '95');
INSERT INTO `studentscore` VALUES ('4', '1', '2', '75');
INSERT INTO `studentscore` VALUES ('5', '2', '2', '85');
INSERT INTO `studentscore` VALUES ('6', '3', '2', '90');
INSERT INTO `studentscore` VALUES ('7', '4', '1', '89');
INSERT INTO `studentscore` VALUES ('8', '4', '2', '88');
INSERT INTO `studentscore` VALUES ('9', '1', '3', '78');
INSERT INTO `studentscore` VALUES ('10', '2', '3', '87');
INSERT INTO `studentscore` VALUES ('11', '3', '3', '98');
INSERT INTO `studentscore` VALUES ('12', '4', '3', '76');
INSERT INTO `studentscore` VALUES ('13', '1', '4', '67');
INSERT INTO `studentscore` VALUES ('14', '2', '4', '76');
INSERT INTO `studentscore` VALUES ('15', '3', '4', '88');
INSERT INTO `studentscore` VALUES ('16', '4', '4', '77');

结果：

2、查询每科成绩第3的学生

sql语句：

select * from studentscore a 
where (select count(*)+1 from studentscore b where a.sourceid = b.sourceid and b.score > a.score) = 3;

结果：

解释：

1、假设a.sourceid=1，a.score=78（这是课程1中第1高的分数）：

select count(*) from studentscore b where 1 = b.sourceid and b.score > 78

如果a.score是课程1中第1高的分数，那表b中课程1中的score比78大的没有1个，所以count(*)是0，返回值就是0

select count(*)+1 from studentscore b where 1 = b.sourceid and b.score > 78

如果我们把count(*)后面加上1，结合上面的分析，可以知道上面sql语句的返回值是1，所以如果a.score是课程1中第1高的分数，那么返回值就是1

2、假设a.sourceid=1，a.score=75（这是课程1中第2高的分数）：

select count(*) from studentscore b where 1 = b.sourceid and b.score > 75

如果a.score是课程1中第2高分数，那表b中课程1中的score比75大的只有1个，所以count(*)是1，返回值就是1

select count(*)+1 from studentscore b where 1 = b.sourceid and b.score > 75

如果我们把count(*)后面加上1，结合上面的分析，可以知道上面sql语句的返回值是2，所以如果a.score是课程1中第2高的分数，那么返回值就是2

以此类推可知：

如果采用count(*)+1这种写法，a.score是课程1中第几高的分数，返回值就是几，所以我们第3高的分数返回值等于3

拓展：

通过上面的分析，查询每科成绩第3的学生也可以这样做：

select * from studentscore a 
where (select count(*) from studentscore b where a.sourceid = b.sourceid and b.score >= a.score) = 3;

3、查询每科成绩前3的学生

sql语句：

select * from studentscore a 
where (select count(*)+1 from studentscore b where a.sourceid = b.sourceid and b.score > a.score) <= 3 
order by sourceid,score desc;

结果：

解释：

可以上面的解释知道，如果采用count(*)+1这种写法，a.score是课程1中第几高的分数，返回值就是几，所以a.score如果是课程1中前3高的分数，那返回值应该小于等于3，然后order by sourceid,score desc代表先按照sourceid排序，在按照score排序

拓展：

通过上面的分析，查询每科成绩前3的学生也可以这样做：

select * from studentscore a 
where (select count(*) from studentscore b where a.sourceid = b.sourceid and b.score >= a.score) <= 3 
order by sourceid,score desc;

4、参考文章：

1、https://blog.csdn.net/sandyagor/article/details/79664086
2、https://blog.csdn.net/qq_31871785/article/details/74946411?utm_medium=distribute.pc_relevant.none-task-blog-baidujs-5