游标的面试题.

[internetcsdn]

--昨晚发的贴打不开,只好重发.

对游标不熟悉,帮帮忙.

create table t1(id int identity(1,1),
name varchar(10),score varchar(10),grade int,[order] int)

insert t1(name,score,grade)
select 'aa','11',85
union all select 'bb','22',84
union all select 'cc','11',86
union all select 'dd','22',90
union all select 'ee','11',81

按score分组,以grade排名,来update表t1的order(名次)
要求:
写proc(用cursor来实现)

[internetcsdn]

打开昨晚的贴,
进来接分吧...........

[aarondella]

CREATE procedure usp_proc

as
set nocount on
declare @num int
declare @name1 varchar(10)
declare @grade int
declare @order1 int
declare @score varchar(10)
declare @count int

/*
create table #t2 (name1 varchar(10),score varchar(10),grade varchar(10),order1 int)
insert into #t2 select name1,score,grade,order1 from t1 order by score,grade desc
*/
declare usp_cursor cursor for select distinct score from t1
open usp_cursor
fetch next from usp_cursor into @score
while @@fetch_status=0
begin
set @num=1
declare usp_cur cursor for select name1,grade,order1 from t1 where score=@score order by grade desc
open usp_cur
fetch next from usp_cur into @name1,@grade,@order1
while @@fetch_status=0
begin
update t1 set order1=@num where name1=@name1 and score=@score and grade=@grade
set @num=@num+1
fetch next from usp_cur into @name1,@grade,@order1
end
close usp_cur
deallocate usp_cur
fetch next from usp_cursor into @score
end
close usp_cursor
deallocate usp_cursor

GO

我把你的表里的name,order 改为了 name1,order1
因为有保留字。
我测过了，应该可以。


测试结果：

id name1 score grade order1
3 cc 11 86 1
1 aa 11 85 2
5 ee 11 81 3
4 dd 22 90 1
2 bb 22 84 2

[aarondella]

declare @Mscore int,@mid int,@id int,@score int
set @mid=0
set @Mscore=(select top 1 score from t1 order by score,grade desc)
declare cur cursor for select id,score from t1 order by score,grade desc
open cur
fetch cur into @id,@score
while @@fetch_status=0
begin
if @Mscore=@score
set @mid=@mid+1
else
set @mid=1
update t1 set order1=@mid where id=@id
set @Mscore=@score
fetch cur into @id,@score

end
close cur
deallocate cur

这个是邹建写的，比我的好多了。