SQL query Removal Duplicate value statement

SQL Single Table/multiple table query removal duplicate record single table distinct multiple table group byGroup  byMust be placed inOrder  byand limit before the error will be************************************************************************************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 is likely to be the same, now it is necessary 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发生这种重复的原因是表设计不周产生的, add a unique index column 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 Name,address a result set that is not duplicated (but with one autoid field, which can be written in the SELECT clause without this column in the actual write) (iv) query duplicationSelect *  fromTableNamewhereIdinch(SelectId fromTableNameGroup  byID having Count(ID)> 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) operation can cause problems,where(A.PEOPLEID,A.SEQ) Such a write hair is not pass!!!