----创建测试数据 declare @t table(id int identity(1,1),aa varchar(10)) insert @t select 'A005' union all select 'A006' union all select 'A100' union all select 'A100' union all select 'A100' union all select 'A100' ----更新 update a set aa = 'A' + rtrim(100 + isnull((select count(*) from @t where id >= (select min(id) from @t where aa = 'A100') and id < a.id),0)) from @t as a where aa = 'A100'----查看 select * from @t/*结果 id aa ----------- ---------- 1 A005 2 A006 3 A100 4 A101 5 A102 6 A103 */
declare @i int set @i = 99 update @t set aa = A+cast(@i as varchar(5)),@i = @i+1 where aa = 'A100'
declare @t table(id int identity(1,1),aa varchar(10))
insert @t
select 'A005' union all
select 'A006' union all
select 'A100' union all
select 'A100' union all
select 'A100' union all
select 'A100' ----更新
update a set aa = 'A' +
rtrim(100 + isnull((select count(*) from @t where id >= (select min(id) from @t where aa = 'A100') and id < a.id),0))
from @t as a
where aa = 'A100'----查看
select * from @t/*结果
id aa
----------- ----------
1 A005
2 A006
3 A100
4 A101
5 A102
6 A103
*/
set @i = 99
update @t set aa = A+cast(@i as varchar(5)),@i = @i+1
where aa = 'A100'