SQL Delete duplicate data method

For example:
ID Name Value
1 a pp
2 a PP
3 B III
4 B pp
5 B pp
6 C pp
7 C pp
8 C III
ID is primary key
Ask for such a result
ID Name Value
1 a pp
3 B III
4 B pp
6 C pp
8 C III

Method 1Deleteyourtablewhere   [ID]    not   inch   ( Select   Max([ID]) fromyourtableGroup    by(Name+value)) Method 2Deletea fromTable A Left   Join( SelectId=min(ID) fromTableGroup    byName,value) b ona.ID=b.idwhereb.ID is   NULL 

SQL statement for querying and deleting duplicate records

A

1, duplicate records in the lookup table are judged by a single field (Peopleid) .Select *  frompeoplewherePeopleidinch(SelectPeopleid fromPeopleGroup  byPeopleid having Count(Peopleid)> 1)2, delete redundant records in the table, duplicate records are judged by a single field (Peopleid), leaving only the smallest rowID recordsDelete  frompeoplewherePeopleidinch(SelectPeopleid fromPeopleGroup  byPeopleid having Count(Peopleid)> 1) androwID not inch(Select min(ROWID) fromPeopleGroup  byPeopleid having Count(Peopleid)>1)3, finding extra duplicate records in a table (multiple fields)Select *  fromVitae awhere(A.PEOPLEID,A.SEQ)inch(SelectPeopleid,seq fromVitaeGroup  byPeopleid,seq having Count(*)> 1)4, delete extra duplicate records (multiple fields) in the table, leaving only the record with ROWID minimumDelete  fromVitae awhere(A.PEOPLEID,A.SEQ)inch(SelectPeopleid,seq fromVitaeGroup  byPeopleid,seq having Count(*)> 1) androwID not inch(Select min(ROWID) fromVitaeGroup  byPeopleid,seq having Count(*)>1)5, find redundant duplicate records (multiple fields) in the table, and do not contain ROWID minimum recordsSelect *  fromVitae awhere(A.PEOPLEID,A.SEQ)inch(SelectPeopleid,seq fromVitaeGroup  byPeopleid,seq having Count(*)> 1) androwID not inch(Select min(ROWID) fromVitaeGroup  byPeopleid,seq having Count(*)>1)
(b) For example, there is a field "name" in Table A, and the "name" value between different records may be the same, now is the need to query out the records in the table, "name" value has duplicate entries;SelectName,Count(*) fromAGroup  byName having Count(*)> 1If you also look at the same gender, the following is true:SelectName,sex,Count(*) fromAGroup  byName,sex having Count(*)> 1(iii) method IDeclare @max integer,@id integerDeclareCur_rowscursorLocal for SelectMain field,Count(*) fromTable nameGroup  byMain field having Count(*)>；1Opencur_rowsFetchCur_rows into @id,@max while @ @fetch_status=0beginSelect @max = @max -1Set RowCount @maxDelete  fromTable namewhereMain field= @idFetchCur_rows into @id,@maxEndClosecur_rowsSet RowCount 0method Two "duplicate records" have two meanings of duplicate records, one is a completely duplicate records, that is, all the fields are duplicated records, second, some key fields duplicate records, such as the Name field is repeated, and the other fields may not repeat or repeat can be ignored. 1, for the first repetition, easier to solve, useSelect distinct *  fromTableName can get a result set with no duplicate records. If the table needs to delete duplicate records (duplicate records retain 1), you can delete them as followsSelect distinct *  into#Tmp fromTableNameDrop TableTableNameSelect *  intoTableName from#TmpDrop Table#Tmp This duplication occurs because the table is poorly designed, and the unique index column is added to resolve. 2, this type of repetition usually requires the first record in the duplicate record to be retained, as follows, assuming that there is a duplicate field name,address, which requires a result set that is unique to both fieldsSelect Identity(int,1,1) asAutoid,*  into#Tmp fromTableNameSelect min(autoid) asAutoid into#Tmp2 from#TmpGroup  byname,autoidSelect *  from#TmpwhereAutoidinch(SelectAutoid from#tmp2) The last select results in a name,address non-repeating result set (but one more autoid field that can be written in the SELECT clause without this column in the actual write)
(iv) Duplication of queriesSelect *  fromTableNamewhereIdinch (SelectId fromTableNameGroup  byID having Count(ID)> 1)

SQL Delete duplicate data method