1, in the interview encountered a problem, is to write a table with ID and name two fields, query out the name duplicate all the data, now listed below:Select * fromXi Awhere(A.username)inch(SelectUsername fromXiGroup byUsername having Count(*)> 1)2, query data after all the data is grouped, and the number of repetitions of repeated data, first listed:Select Count(username) as 'Number of repetitions', username fromXiGroup byUsername having Count(*)>1 Order byUsernamedesc3, to see the results of others, now listed: Query and delete duplicate records method Daquan1, duplicate records in the lookup table are based on a single field (Peopleid) to determine the 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(*)>1 If the same gender is also found, the following are:SelectName,sex,Count(*) fromAGroup byName,sex having Count(*)> 1(iii) method one declare@max integer,@idIntegerdeclare cur_rowscursorLocal for SelectMain field,Count(*) fromTable nameGroup byMain field having Count(*)>; 1OpenCur_rowsfetch cur_rows into @id,@maxwhile @ @fetch_status=0beginselect@max = @max -1Set RowCount @maxdelete fromTable namewhereMain field= @idfetchCur_rows into @id,@maxendcloseCur_rowssetRowCount 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, it is easier to solve, use Selectdistinct * 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 the select by following these methodsdistinct * into#Tmp fromTablenamedropTableTablenameselect* intoTableName from#TmpdropTable#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 fields selectIdentity(int,1,1) asAutoid,* into#Tmp fromTablenameselectmin(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 duplicate select* fromTableNamewhereIdinch(SelectId fromTablenamegroup byIdhavingCount(ID)> 1)
SQL finds all of the recorded data in a single table