多表查查查查解决方法

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 allselect 2,'李四' union allselect 3,'王五'insert into C select 10,'语文' union allselect 20,'数学' union allselect 30,'英语' insert into SCselect 1,10,90 union allselect 1,20,89 union allselect 1,30,88 union allselect 2,10,80 union allselect 2,20,79 union allselect 2,30,78 union allselect 3,10,60 union allselect 3,20,66 union allselect 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=1Sno         Sname      Cno         Cname      Score----------- ---------- ----------- ---------- -----------1           张三         10          语文         901           张三         20          数学         893           王五         30          英语         100(3 行受影响)