题目
数据表结构为
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
－－－－注：先找班级distinct(class) ，把班级放在游标里面，然后根据班级照前两名成绩的学生信息放在临时表里