;with maco as
(select row_number() over (partition by scourse order by score desc) as id,
* from course)

select * from maco where id<3
/*
id                   sname      scourse    score
-------------------- ---------- ---------- ----------------------
1                    张三         数学         98
2                    李四         数学         95
1                    赵六         英语         100
2                    张三         英语         99
*/

------解决方案--------------------
SQL code

select sname,
       scourse,
       score
from course t
where sname in(select top 2 sname from course where scourse = t.scourse order by score desc)

------解决方案--------------------
SQL code
select distinct B.sname,B.scourse,B.score
from course A
cross apply (select top 2 sname,scourse,score 
                from course  
                where  scourse =A.scourse
                order by score desc) B
                
/*
sname      scourse    score
---------- ---------- ----------------------
李四         数学         95
张三         数学         98
张三         英语         99
赵六         英语         100

(4 行受影响)