SQL点滴一:借row_number()函数更新排序字段值

create table t1(c1 int ,c2 int)
insert t1(c1,c2) values(5,10)
insert t1(c1,c2) values(3,20)
insert t1(c1,c2) values(4,30)
insert t1(c1,c2) values(2,40)
insert t1(c1,c2) values(1,50)

select * from t1

c1          c2
----------- -----------
5           10
3           20
4           30
2           40
1           50

update t1 set t1.c1 =t.c1
from
(
select
row_number() over (order by c2) as c1,
c2
from t1
) as t
where t.c2=t1.c2

select * from t1

c1          c2
----------- -----------
1           10
2           20
3           30
4           40
5           50

drop table t1

 

参考：http://blog.csdn.net/Star8816/archive/2008/10/08/3033888.aspx