mysql组内排序

mysql 查询时需要查出分组后的结果的TOPN

（(mysql 5.7)低版本数据库没有现成的函数）

思路：本表和本表做内连接，求前三名，筛选数据条件是，分组后，组内记录比当前这一条记录大的记录最多只有2个
（这样加上当前这条记录就是前三条记录）。

例题 + 解答

 一张成绩表包括一个班级里多科目多名学生的所有成绩，要求查询各科前三名成绩

表结构如下：

create table scoreinfo(
	ScoreId int auto_increment primary key,
	StuId int not null,
	SubjectId int not null,
	SubjectNamw varchar(15) not null,
	score int 
);

插入表数据

INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 1, 1,'语文', 87);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 2, 1,'语文',79 );
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 3, 1,'语文', 85);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 4, 1,'语文', 87);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 5, 1,'语文', 73);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 6, 1,'语文', 92);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 7, 1,'语文', 84);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 8, 1,'语文', 65);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 9, 1,'语文', 44);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 10, 1,'语文', 57);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 1, 2,'数学', 93);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 2, 2,'数学', 84);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 3, 2,'数学', 88);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 4, 2,'数学', 58);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 5, 2,'数学', 63);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 6, 2,'数学', 73);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 7, 2,'数学', 93);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 8, 2,'数学', 82);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 9, 2,'数学', 81);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 10, 2,'数学', 90);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 1, 3,'英语', 72);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 2, 3,'英语', 35);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 3, 3,'英语', 64);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 4, 3,'英语', 66);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 5, 3,'英语', 70);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 6, 3,'英语', 69);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 7, 3,'英语', 78);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 8, 3,'英语', 84);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 9, 3,'英语', 66);
INSERT INTO `scoreinfo` (`StuId`, `SubjectId`,`SubjectName`, `score`) VALUES ( 10, 3,'英语', 53);

查询各科TOP 3
解答

SELECT
	S.*
FROM
	scoreinfo S
WHERE
	(
		SELECT
			count(*)
		FROM
			scoreinfo
		WHERE
			score > S.score
		AND S.SubjectId = SubjectId
	) <= 2