create table #t(GUID varchar(10), Unitcoding varchar(20), Name char(5), Content1 varchar(10))
insert #t
select 'wqe23', '100100003003', 'name1', 'nnnn'
union all
select 'qwe231', '100100003003', 'name2', 'mmmm'
union all
select 'wdw123', '200100006001', 'name3', 'nnn2'select GUID,
Unitcoding + right('0000' +
(select convert(varchar,count(1)) from #t t2 where t1.Unitcoding = t2.Unitcoding and t1.GUID < = t2.GUID), 4 ) [New Unitcoding],
Name, Content1
from #t t1/*
GUID New Unitcoding Name Content1
---------- ---------------------------- ----- ----------
wqe23 1001000030030001 name1 nnnn
qwe231 1001000030030002 name2 mmmm
wdw123 2001000060010001 name3 nnn2
*/drop table #t
insert #t
select 'wqe23', '100100003003', 'name1', 'nnnn'
union all
select 'qwe231', '100100003003', 'name2', 'mmmm'
union all
select 'wdw123', '200100006001', 'name3', 'nnn2'select GUID,
Unitcoding + right('0000' +
(select convert(varchar,count(1)) from #t t2 where t1.Unitcoding = t2.Unitcoding and t1.GUID < = t2.GUID), 4 ) [New Unitcoding],
Name, Content1
from #t t1/*
GUID New Unitcoding Name Content1
---------- ---------------------------- ----- ----------
wqe23 1001000030030001 name1 nnnn
qwe231 1001000030030002 name2 mmmm
wdw123 2001000060010001 name3 nnn2
*/drop table #t
30多万条数据!麻烦!