- Test data / * ----------------------------------- ------------------ * / ID PID --------------------- 1 11 12 23 33 33 3
(The number of rows affected is 6 lines) First, how to query Table has repeated record select *, count (1) as rownumfrom ttgroup by id, pidhaving count (1)> 1ID pid rownum --------- - - ---------------------- 1 1 23 3 3
(The number of rows of rows) Method 1: Using Distinct and Temporary table if Object_id ('tempdb .. # tmp') is not nulldrop table #tmpselect distinct * into #tmp from Tttruncate Table Ttinsert Into Tt Select * from # # TMP Method 2: Add Label Alter Table Tt Add NewId Int Idnessity (1) Go Delete from TT WHERE EXISTS (SELECT 1 from TT A WHERE A.NEWID> tt.newid and tt.id = A.ID AND TT. PID = a.pid) Goalter Table Tt Drop Column newidgo / * Note, Oracle is simpler than column ROWID, you can directly use rowid * // * delete from TT WHERE EXISTS (SELECT 1 from tt a a.rowid > tt.rowid and tt.col1 = a.col1 and tt.col2 = a.col2) * / - Test results / * --------------------- -------- SELECT * from TT ----------------------------- * / id pid ----- ------ ----------- 1 12 23 3
(The number of rows affects is 3 lines)
