mysql 查询每门功成绩最好的前两名,在SQL SERVER中,怎么样查询每门功课成绩最好的前两名,帮我写一下,谢谢,请补充:...

create table #SC(

S# varchar(10), --学号

C# varchar(10), --课号

score decimal(18,1) --成绩

)

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' , 80)

insert into #sc values('03' , '01' , 80)

insert into #sc values('03' , '02' , 80)

insert into #sc values('03' , '03' , 80)

insert into #sc values('04' , '01' , 50)

insert into #sc values('04' , '02' , 30)

insert into #sc values('04' , '03' , 20)

insert into #sc values('05' , '01' , 76)

insert into #sc values('05' , '02' , 87)

insert into #sc values('06' , '01' , 31)

insert into #sc values('06' , '03' , 34)

insert into #sc values('07' , '02' , 89)

insert into #sc values('07' , '03' , 98)

create table #temp

(

S# varchar(10), --学号

C# varchar(10), --课号

score decimal(18,1) --成绩

)

insert into #temp

select top 2 *from #SC where C#='01' order by score desc

insert into #temp

select top 2 *from #SC where C#='02' order by score desc

insert into #temp

select top 2 *from #SC where C#='03' order by score desc

select *from #temp

drop table #temp

drop table #SC

运行结果

S#C#score

010180.0

030180.0

010290.0

070289.0

010399.0

070398.0

取消

评论