处理重复记录的次数，重复一次的用A，其次用B，依次。。。。

 

处理重复记录的次数，重复一次的用A ，其次用B ，依次。。。。

--建立测试环境

create table #TestA(col1 varchar(10),col2 int,col3 varchar(20))

insert into #TestA

select '111',1,'adsf' union all

select '111',2,'sdfd' union all

select '111',3,'tyuio' union all

select '112',1,'dfgg' union all

select '222',1,'sdfg' union all

select '222',2,'vbmv' union all

select '222',3,'tuyi' union all

select '200',4,'dfhfgh' union all

select '111',1,'ert' union all

select '111',2,'hjkj' union all

select '222',1,'sdfg' union all

select '222',2,'vbmv' union all

select '222',3,'tuyi' union all

select '111',1,'ppp'

 

--测试

--建立一个字符串的虚拟表，用于特殊处理

select top 26 identity(int,65,1) AsciiID

into #AsciiID

from sysobjects A,sysobjects B

--建立一个自动增量的复本

select *,identity(int,1,1) TID into #testAA from #testA

--按递增取得重复值的第几次

select *,(select count(*)

from #testAA B

where A.col1=B.col1 and A.col2=B.col2 and B.TID<=A.TID

group by B.col1,B.col2

) DoubleTime

from #testAA A

--进行测试查询

select C.col1,C.col2,C.col1+isnull(D.AsciiChar,'') NewCol1

from

(

       select *,(

       select count(*)

       from #testAA B

       where A.col1=B.col1 and A.col2=B.col2 and B.TID<=A.TID

       group by B.col1,B.col2

       ) DoubleTime

       from #testAA A

 

) C

left join

(

       select char(AsciiID) AsciiChar,AsciiID-63 TID

       from #AsciiID

) D

on C.DoubleTime=D.TID

--order by C.col1

--显示结果

/*

Col1 Col2 NewCol1

111 1      111

111 2      111

111 3      111

112 1      112

222 1      222

222 2      222

222 3      222

200 4      200

111 1      111A

111 2      111A

222 1      222A

222 2      222A

222 3      222A

111 1      111B

 

*/

--删除测试环境

drop table #testA

drop table #testAA

drop table #AsciiID