重复字段后增加值
表结构：
id code
1 978777
2 978777
3 978777
4 978775
5 978775
6 978776
7 978776
8 978776
9 978776
10 978776
替换后结果要求：
id code
1 978777
2 97877701
3 97877702
4 978775
5 97877501
6 978776
7 97877601
8 97877602
9 97877603
10 97877604
[解决办法]

create table jer
(id int, code varchar(10))

insert into jer
 select 1, '978777' union all
 select 2, '978777' union all
 select 3, '978777' union all
 select 4, '978775' union all
 select 5, '978775' union all
 select 6, '978776' union all
 select 7, '978776' union all
 select 8, '978776' union all
 select 9, '978776' union all
 select 10, '978776'


-- 更新
update a
 set a.code=a.code+case when b.rn=0 then ''
                        else replicate('0',2-len(b.rn))+rtrim(b.rn) end
 from jer a
 inner join
 (select id,code,row_number() over(partition by code order by id)-1 'rn'
  from jer) b on a.id=b.id


-- 结果 
select id,code from jer

/*
id          code
----------- ---------- 
 
1           978777
2           97877701
3           97877702
4           978775
5           97877501
6           978776
7           97877601
8           97877602
9           97877603
10          97877604

(10 row(s) affected)
*/

[解决办法]

引用:

表里不仅只有10条，后面还有很多重复的。要怎么写呢？

需看最大重复数是多少? 2楼SQL的重复编号是2位数,
如有更大,修改replicate()函数中的位数即可,如下SQL注释..

-- 更新
update a
 set a.code=a.code+case when b.rn=0 then ''
                        else replicate('0',2-len(b.rn))+rtrim(b.rn) end  --> 2是总位数,可修改为3,4,5,6...
 from jer a
 inner join
 (select id,code,row_number() over(partition by code order by id)-1 'rn'
  from jer) b on a.id=b.id