--用游标处理的话.declare tb cursor local for select id,grade from t1 order by grade desc declare @i int,@id int,@grade int,@pgrade intset @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
update t1 set [order]=(select count(distinct grade) from t1 where grade>=a.grade and score=a.score) from t1 a
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)
游标的写法 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
select id,grade from t1 order by grade desc
declare @i int,@id int,@grade int,@pgrade intset @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
from t1 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)
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