SQL Duplicate record query

SQL statements:1.Select COUNT (Distinct ann_id) fromDmdb.t_bond_ann_att_info where Last_update_time >='2018-03-09 14:00:00';2.Select COUNT (ID) fromDmdb.t_bond_ann_info where Create_time >='2018-03-09 14:00:00';3.Select a.* fromdmdb.t_bond_ann_info awhere a.id not inch(select ann_id fromT_bond_ann_att_info where Last_update_time>='2018-03-09 14:00:00'   ) andA.create_time >='2018-03-09 14:00:00';4.Select distinct a.ann_id fromdmdb.t_bond_ann_att_info awhere a.ann_id not inch(SELECT ID fromT_bond_ann_info where Create_time>='2018-03-09 11:10:00'   ) andLast_update_time >='2018-03-09 11:10:00';5.select * fromT_bond_ann_att_info where ann_idinch('13441','13442')6.delete fromT_bond_ann_att_info where ann_idinch('13441','13442')7.Select * fromdmdb.t_bond_ann_info ORDER by Create_time Desclimit8008.select * fromDmdb.t_bond_ann_att_info where ann_id=15068--------------------------------------------------------------------- ------1, duplicate records in the lookup table are based on a single field (Peopleid) to determine the select* fromPeoplewhere Peopleidinch(Select Peopleid fromPeople GROUP by Peopleid have count (Peopleid) > 1) Example two: select* fromtesttable where Numeberinch(SELECT number fromPeople GROUP by number have count (number) > 1) You can find the same number of records in the TestTable table2, delete redundant records in the table, repeat records are based on a single field (Peopleid), leaving only the smallest rowid record delete frompeople where Peopleidinch(Select Peopleid fromPeople GROUP by Peopleid have count (Peopleid) > 1) androwID not inch(select min (rowid) fromPeople GROUP by Peopleid has count (Peopleid) >1)3, finding extra duplicate records (multiple fields) in a table select*fromvitae awhere (a.peopleid,a.seq)inch(Select Peopleid,seq fromVitae GROUP BY PEOPLEID,SEQ have count (*) > 1)4, delete extra duplicate records (multiple fields) in the table, leaving only the rowID minimum record delete fromvitae awhere (a.peopleid,a.seq)inch(Select Peopleid,seq fromVitae GROUP BY PEOPLEID,SEQ have count (*) > 1) androwID not inch(select min (rowid) fromVitae GROUP BY PEOPLEID,SEQ has count (*) >1)5, find extra duplicate records in table (multiple fields), do not contain ROWID minimum records select*fromvitae awhere (a.peopleid,a.seq)inch(Select Peopleid,seq fromVitae GROUP BY PEOPLEID,SEQ have count (*) > 1) androwID not inch(select min (rowid) fromVitae GROUP BY PEOPLEID,SEQ has 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; Select Name,count (*) from A Group by Name have Count (*) > 1If you also look at the same gender is the same large as follows: Select Name,sex,count (*) from A Group by Name,sex have Count (*) > 1method Two has two meanings of duplicate records, one is a completely duplicate record, that is, all the fields are duplicated records, and the second is some key field duplicate records, such as the Name field repeats, and other fields may not be repeated or can be ignored. 1, for the first repetition, it is easier to solve, use SELECT 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 the select distinct as follows* INTO#Tmp FromtablenameDrop Tabletablenameselect* Into TableName from#TMPdrop table#TMPThis duplication occurs because the table is poorly designed and the unique index columns are 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 that both fields have a unique result set of select identity (int,As Autoid, * into#Tmp FromtablenameSelect min (autoid) as Autoid into#Tmp2 from #Tmp group Byname,autoidSelect* from #Tmp where autoid in (select Autoid from #tmp2)The last select obtains a name,address result set (but one autoid field that can be written in the SELECT clause without this column in the actual write) (iv) query duplicate select* fromTableName where IDinch(SELECT ID fromtablename GROUP BY ID has count (ID)> 1 )

SQL Duplicate record query