有学生科目成绩表这三张表要求查处各个科目的前三名成绩

create table student
(
 sid int identity(1,1) primary key,
 sname varchar(20) not null
)

create table coures
(
  cid int identity(1,1) primary key,
  cname varchar(20)
)

create table score
(
   scid int identity(1,1) primary key,
   sid int not null,
   cid int not null,
   result float
)


insert into student values('张学友')
insert into student values('吴孟达')
insert into student values('张柏芝')
insert into student values('曹阿瞒')
insert into student values('张牛俊')

insert into coures values('计算机科学与应用')
insert into coures values('高等数学')
insert into coures values('线性代数')

insert into score values(1,1,80)
insert into score values(1,2,78)
insert into score values(1,3,59)
insert into score values(2,1,84)
insert into score values(2,2,90)
insert into score values(2,3,45)
insert into score values(3,1,23)
insert into score values(3,2,45)
insert into score values(3,3,87)
insert into score values(4,1,100)


select * from student
select * from coures
select * from score


--查询出 各个科目的前三名(圆满解决)
select cid,result from score s group by cid,result  having result
in
(
   select top 3 result  from score where cid = s.cid order by result desc
)
order by cid,result desc