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