score table:

CREATE TABLE `score` (
  `student_id` int(10) DEFAULT NULL,
  `class_id` int(10) DEFAULT NULL,
  `score` int(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Field student ID student ID, class ID: class ID, score: Score
Data preparation:

insert into score values(1,1,100),(2,1,93),(3,1,89),(4,1,96),(5,2,98),(6,2,97),(7,2,90),(8,2,88),(9,1,96);

The table structure is as follows:

mysql> select * from score;
+------------+----------+-------+
| student_id | class_id | score |
+------------+----------+-------+
|          1 |        1 |   100 |
|          2 |        1 |    93 |
|          3 |        1 |    89 |
|          4 |        1 |    96 |
|          5 |        2 |    98 |
|          6 |        2 |    97 |
|          7 |        2 |    90 |
|          8 |        2 |    88 |
|          9 |        1 |    96 |
+------------+----------+-------+
9 rows in set (0.00 sec)

1. Take the top two students of each class (including the second in parallel)

mysql> select * 
from score s1 
where (select count(0) from score s2 where s1.class_id = s2.class_id and s1.score < s2.score) < 2;
+------------+----------+-------+
| student_id | class_id | score |
+------------+----------+-------+
|          1 |        1 |   100 |
|          4 |        1 |    96 |
|          5 |        2 |    98 |
|          6 |        2 |    97 |
|          9 |        1 |    96 |
+------------+----------+-------+
5 rows in set (0.00 sec)

sql explanation: take the data in table s1. For the data with the same class ID and s2 class ID, the data entry of s2, which is larger than the score score of s1, must be less than 2

Or use left join:

mysql> select s1.* 
from score s1 
left join score s2 on s1.class_id = s2.class_id and s1.score<s2.score 
group by s1.class_id,s1.student_id,s1.score 
having count(s2.student_id)<2;
+------------+----------+-------+
| student_id | class_id | score |
+------------+----------+-------+
|          1 |        1 |   100 |
|          4 |        1 |    96 |
|          9 |        1 |    96 |
|          5 |        2 |    98 |
|          6 |        2 |    97 |
+------------+----------+-------+
5 rows in set (0.00 sec)

2. Take student score data and represent ranking

mysql> select s1.*,(select count(0) + 1 from score s2 where s2.score > s1.score)rank from score s1;
+------------+----------+-------+------+
| student_id | class_id | score | rank |
+------------+----------+-------+------+
|          1 |        1 |   100 |    1 |
|          2 |        1 |    93 |    6 |
|          3 |        1 |    89 |    8 |
|          4 |        1 |    96 |    4 |
|          5 |        2 |    98 |    2 |
|          6 |        2 |    97 |    3 |
|          7 |        2 |    90 |    7 |
|          8 |        2 |    88 |    9 |
|          9 |        1 |    96 |    4 |
+------------+----------+-------+------+
9 rows in set (0.00 sec)

sql explanation: just display the items in s2 that have larger scores than those in s1 (add 1 for count)

3. Take student performance data to show class ranking

mysql> select s1.*
,(select count(0) + 1 
from score s2 
where s1.class_id = s2.class_id 
and s2.score > s1.score
)rank 
from score s1 
order by class_id,rank;
+------------+----------+-------+------+
| student_id | class_id | score | rank |
+------------+----------+-------+------+
|          1 |        1 |   100 |    1 |
|          4 |        1 |    96 |    2 |
|          9 |        1 |    96 |    2 |
|          2 |        1 |    93 |    4 |
|          3 |        1 |    89 |    5 |
|          5 |        2 |    98 |    1 |
|          6 |        2 |    97 |    2 |
|          7 |        2 |    90 |    3 |
|          8 |        2 |    88 |    4 |
+------------+----------+-------+------+
9 rows in set (0.00 sec)

Same as before, but only the same data items of the class need to be calculated in the filter condition

4. Take the top two of each class (only the data in front will be taken in parallel)

Reference documents:
https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql