SQL题目

数据表结构为

create table Ques1

(
 id int identity(1,1) primary key,--主键
 [Name] nvarchar(50) not null,--学生姓名
 Score int not null,--学生成绩
 Class int not null--学生班级
)

要查询每班前两名学生的信息

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

declare @class int
create table #temptable(id int,[name] nvarchar(50),score int,class int)

declare curClass cursor for select distinct(class) from stuscore
for read only
open curClass
fetch next from curClass into @class
while @@FETCH_STATUS=0
begin
insert into #temptable select top 2 * from stuscore where class=@class order by score desc
--print @class
fetch next from curClass into @class
end
close curClass
deallocate curClass
select * from #temptable
drop table #temptable