MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table SC ( SId varchar(10) comment "Student ID",
                  CId varchar(10) comment "Course ID",
                  score decimal(18,1) comment "Test Score" );
insert into SC values('01' , '01' , 100);
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 60);
insert into SC values('02' , '03' , 70);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 70);
insert into SC values('03' , '03' , 50);
insert into SC values('04' , '01' , 90);
insert into SC values('04' , '02' , 90);
insert into SC values('04' , '03' , 90);
insert into SC values('04' , '03' , 70);
SELECT * 
FROM SC;

SELECT SId, CId, SUM(score) score
FROM SC a
GROUP BY SId, CId
ORDER BY CId, score DESC;


SId
CId
score


01
01
100.0

01
01
80.0

01
02
90.0

01
03
99.0

02
01
70.0

02
02
60.0

02
03
60.0

02
03
70.0

03
01
80.0

03
01
80.0

03
02
70.0

03
03
50.0

04
01
90.0

04
02
90.0

04
03
90.0

04
03
70.0




SId
CId
score


01
01
180.0

03
01
160.0

04
01
90.0

02
01
70.0

04
02
90.0

01
02
90.0

03
02
70.0

02
02
60.0

04
03
160.0

02
03
130.0

01
03
99.0

03
03
50.0


SELECT SId, CId, score
FROM (
SELECT SId, CId, score,
       @rownumber := IF(@course = CId, @rownumber + 1, 1) rownumber,
       @course := CId
FROM ( SELECT SId, CId, SUM(score) score
       FROM SC a
       GROUP BY SId, CId ) data, 
     ( SELECT @rownumber:=0, @course:='' ) vars
ORDER BY CId, score DESC
) data
WHERE rownumber <= 3


SId
CId
score


01
01
180.0

03
01
160.0

04
01
90.0

04
02
90.0

01
02
90.0

03
02
70.0

04
03
160.0

02
03
130.0

01
03
99.0

SId	CId	score
01	01	100.0
01	01	80.0
01	02	90.0
01	03	99.0
02	01	70.0
02	02	60.0
02	03	60.0
02	03	70.0
03	01	80.0
03	01	80.0
03	02	70.0
03	03	50.0
04	01	90.0
04	02	90.0
04	03	90.0
04	03	70.0

SId	CId	score
01	01	180.0
03	01	160.0
04	01	90.0
02	01	70.0
04	02	90.0
01	02	90.0
03	02	70.0
02	02	60.0
04	03	160.0
02	03	130.0
01	03	99.0
03	03	50.0