create table S
(Sno int,    
Sname nvarchar(10))

create table C
(Cno int,
 Cname nvarchar(10))

create table SC
(Sno int, 
 Cno int,
 Score int)
 
 
 insert into S 
 select 1,'张三'  union all
select 2,'李四' union all
select 3,'王五'

insert into C 
select 10,'语文' union all
select 20,'数学' union all
select 30,'英语' 


insert into SC
select 1,10,90 union all
select 1,20,89 union all
select 1,30,88 union all
select 2,10,80 union all
select 2,20,79 union all
select 2,30,78 union all
select 3,10,60 union all
select 3,20,66 union all
select 3,30,100 

;with cte as
(
 select *,rowNum=dense_rank() over(partition by Cno order by Score desc) from SC
)
select t1.Sno,t2.Sname,t1.Cno,t3.Cname,t1.Score from cte t1 
left join S t2 on t1.Sno=t2.Sno left join C t3 on t1.Cno=t3.Cno 
where t1.rowNum=1


Sno         Sname      Cno         Cname      Score
----------- ---------- ----------- ---------- -----------
1           张三         10          语文         90
1           张三         20          数学         89
3           王五         30          英语         100

(3 行受影响)