游标的面试题.

[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]

csdn重开后,见到了一些未见过的星.

[internetcsdn]

csdn重开后,见到了一些未见过的星.

[zjcxc]

--一句就可以了

update t1 set [order]=(select count(distinct grade) from t1 where grade>=a.grade)
from t1 a

[zjcxc]

--用游标处理的话.

declare tb cursor local for
select id,grade from t1 order by grade desc
declare @i int,@id int,@grade int,@pgrade int

set @i=1
open tb
fetch next from tb into @id,@grade
while @@fetch_status=0
begin
if @grade<>isnull(@pgrade,@grade) set @i=@i+1
print @grade
update t1 set [order]=@i where id=@id
set @pgrade=@grade
fetch next from tb into @id,@grade
end
close tb
deallocate tb

[xiangsu]

強悍 `!!

[hdslah]

update t1 set [order]=(select count(distinct grade) from t1 where grade>=a.grade and score=a.score)
from t1 a

[LoveSQL]

update a
set [order]=(select count(id) from t1 where grade<=a.grade and score=a.score)
from t1 a
--测试结果
id name score grade order
----------- ---------- ---------- ----------- -----------
15 ee 11 81 1
11 aa 11 85 2
13 cc 11 86 3
12 bb 22 84 1
14 dd 22 90 2

(5 row(s) affected)

[LoveSQL]

游标的写法
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 [order]=@mid where id=@id
set @Mscore=@score
fetch cur into @id,@score

end
close cur
deallocate cur