如何简单来实现一条根据_id1,_id2的索引顺序,更新id的序号值,如下所述如表
id _id1 _id2
null a c
null a d
null a f
null b c
null b e执行后:id _id1 _id2
1 a c
2 a d
3 a f
1 b c
2 b e
id _id1 _id2
null a c
null a d
null a f
null b c
null b e执行后:id _id1 _id2
1 a c
2 a d
3 a f
1 b c
2 b e
declare @tab table(id int,_id1 varchar(10),_id2 varchar(10))
insert @tab values(null,'a','e')
insert @tab values(null,'a','d')
insert @tab values(null,'a','f')
insert @tab values(null,'b','c')
insert @tab values(null,'b','e')select * from @tabselect _id1,_id2,[排名]=(select count(1) from @tab where _id1=a._id1 and _id2<a._id2)+1 from @tab a
order by _id1,_id2
update a
set id = #t.id1
from a,#t
where a._id1 = #t._id1 and a._id2 = #t._id2
_id1 _id2 排名
---------- ---------- -----------
a d 1
a e 2
a f 3
b c 1
b e 2(所影响的行数为 5 行)
再来啊点点星灯:
如果原记录有重行就不行了,需要改进