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

数据表结构为

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

select * from #TempResultTable
drop table #TempResultTable

如果大家有更好的办法欢迎和大家一起分享,请留言.万分感谢