求一个更新语句

[jmu_xiaocai]

396028 0
396028 0
396028 0
214014 0
396028 0

Uid SortIndex

------------------要把这个表更新为

396028 1
396028 2
396028 3
214014 1
396028 4

-
就是uid相同的SortIndex递增

[百年树人]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Uid] int,[SortIndex] int)
insert [tb]
select 396028,0 union all
select 396028,0 union all
select 396028,0 union all
select 214014,0 union all
select 396028,0
go

alter table tb add tmpid int identity(1,1);
go

update t
set SortIndex=(select count(1)+1 from tb where uid=t.uid and tmpid<t.tmpid)
from tb t
go

alter table tb drop column tmpid;
go

select * from tb
/**
Uid         SortIndex
----------- -----------
396028      1
396028      2
396028      3
214014      1
396028      4

(5 行受影响)
**/