The number of times record duplication is processed.
Second, use B
, In turn ....-- Create Table # TESTA (col1 varchar (10), col2 int, col3 varchar (20) insert into # testaselect '123', 1, 'adsf 'Union allselect '123', 2, 'sdfs' Union allselect '123', 3, 'tyuio 'Union allselect '123', 1, 'dfgg 'Union allselect '123', 1, 'sdfg' Union allselect '123', 2, 'vbmv' Union allselect '123', 3, 'tuyi' Union allselect '000000', 4, 'dfhfgh' Union allselect '000000', 1, 'ert 'Union allselect '000000', 2, 'hjkj 'Union allselect' 222 ', 1, 'sdfg' Union allselect '000000', 2, 'vbm' Union allselect '000000', 3, 'tuyi' Union allselect' 111 ', 1, 'ppp' -- Test -- creates a virtual table of strings for special processing of select top 26 identity (INT, 65,1) asciiidinto # asciiidfrom sysobjects A, sysobjects B -- create an automatic incremental copy select *, identity (INT, 1, 1) TID into # testaa from # Testa -- the number of times that duplicate values are obtained in ascending order *, (select count (*) from # testaa B where. col1 = B. col1 and. col2 = B. col2 and B. TID <=. TID group by B. col1, B. col2) doubletimefrom # testaa A -- test and query select C. col1, C. col2, C. col1 + isnull (D. asciichar, '') newcol1from (select *, (select count (*) from # testaa B where. col1 = B. col1 and. col2 = B. col2 and B. TID <=. TID group by B. col1, B. col2) doubletime from # testaa A) cleft join (select char (asciiid) asciichar, AsciiID-63 TID from # asciiid) Don C. doubletime = D. TID -- order by C. col1 -- display result/* col1 col2 newcol1111 1 111111 2 111111 3 111112 1 112222 1 222222 2 222222 3 222200 4 200111 1 111a111 2 111a222 1 222a222 2 222a222 3 222a111 1 111b */-- delete test environment drop table # testaddrop table # testaaddrop table # asciiid
