[原创]一道京东商城的面试题的解决方法

时间：2011-01-08 来源：紫の焰

数据表结构为

create table Ques1

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

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

use db20110107
--定义临时表以存储结果
create table #TempResultTable
(
id int,
[Name] nvarchar(50),
Score int,
Class int
)
--定义游标
declare Cursor_SelectResult cursor for
select * from Ques1 order by class asc,score desc
--打开游标
open Cursor_SelectResult

declare @id int,@Name nvarchar(50),@Score int,@Class int --接受记录字段的变量
declare @dealedThisClassStudents int,@dealedCurrentClass int
set @dealedThisClassStudents = 0
set @dealedCurrentClass = -1

fetch Cursor_SelectResult into @id,@name,@Score,@Class

while(@@fetch_status=0)
begin
print(Convert(nvarchar(50),@id)+'---'+@name+'---'+Convert(nvarchar(50),@score)+'---'+Convert(nvarchar(50),@class))

if(@class=@dealedCurrentClass)
begin
  if(@dealedThisClassStudents<2)
  begin
   insert into #TempResultTable values(@id,@name,@Score,@Class)
   set @dealedThisClassStudents = @dealedThisClassStudents + 1
   print('该条已经插入')
  end
  else if(@dealedThisClassStudents>=2)
  begin
   set @dealedThisClassStudents = @dealedThisClassStudents + 1
  end
end
else if(@class != @dealedCurrentClass)
begin
  set @dealedCurrentClass = @class
  set @dealedThisClassStudents = 0
  insert into #TempResultTable values(@id,@name,@Score,@Class)
  print('该条已经插入')
  set @dealedThisClassStudents = @dealedThisClassStudents + 1
end
fetch Cursor_SelectResult into @id,@name,@Score,@Class

end
close Cursor_SelectResult
deallocate Cursor_SelectResult