create table table1(drugname varchar(100)) insert into table1 select 'asdfcdd' union all select 'asdfrtyt' union all select 'bdfsdf' union all select 'bdfsgrhg' select *,identity(int,1,1) as id into # from # select substring(drugname,1,4)+cast((select 1+count(1) from # where id>A.id and substring(drugname,1,4)=substring(A.drugname,1,4)) as varchar(10)) from # A
Select asdfcdd as asdf1 .....from table1
insert into table1
select 'asdfcdd' union all
select 'asdfrtyt' union all
select 'bdfsdf' union all
select 'bdfsgrhg'
select *,identity(int,1,1) as id into # from #
select substring(drugname,1,4)+cast((select 1+count(1) from # where id>A.id and substring(drugname,1,4)=substring(A.drugname,1,4)) as varchar(10))
from # A
只是将所有前面相同的转化成后面加1.2.3.........
例如
asdasdasdasda
asdasdasdasfgh
asdasdasdjkjl简化后变成
先简化
asdasdasdasda
asdasdasdasfgh
成
asdasdasdas1
asdasdasdas2然后再简化
asdasdasdas1
asdasdasdas2
asdasdasdjkjl
成
asdasdasd1
asdasdasd2
asdasdasd3如此类推