重复字段后增加值
表结构：
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)
*/
-- 更新
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
