mysql delete duplicate records sql statement and query duplicate records

method 1
delete yourtable where [id] not in (select max ([id]) from yourtable group by (name + value))

Method 2
delete a from table a left join (select (id) from table group by name, value) b on a.id = b.id where b.id is null

Query and delete duplicate records sql statement

1, look for redundant records in the table, duplicate records are based on a single field (peopleid) to determine
select * from people where people in in select (select peopleid from people group by peopleid having count (peopleid)> 1)

2, delete redundant records in the table, duplicate records are based on a single field (peopleid) to judge, leaving only the smallest rowid record
delete from people where people id in select people id from people group by people id having count (peopleid)> 1) and row id not in (select min (row id) from people group by people id having count (people id)> 1)

3, look for redundant records in the table (multiple fields)
select * from vitae a where (a.peopleid, a.seq) in (select peopleid, seq from vitae group by peopleid, seq having count (*)> 1)

4, delete redundant records in the table (multiple fields), leaving only the smallest rowid record
delete from vitae a where (a.peopleid, a.seq) in (select peopleid, seq from vitae group by peopleid, seq having count (*)> 1) and rowid not in (select min (rowid) from vitae group by peopleid , seq having count (*)> 1)

5, look for redundant records in the table (multiple fields), does not contain the smallest rowid record
select * from vitae a (a.peopleid, a.seq) in (select peopleid, seq from vitae group by peopleid, seq having count (*)> 1) and rowid not in (select min (rowid) from vitae group by peopleid, seq having count (*)> 1)

(two)
For example, in a table there is a field "name", and the "name" value between different records may be the same, now it is necessary to query out between the records in the table, "name" value is repeated If you also check the gender are the same as follows: select name, count (*) from a group by name, sex having count (*) from a group by name having count *)> 1

(three)
method one
declare @max integer, @ id integer
declare cur_rows cursor local for select main field, count (*) from table name group by the main field having count (*)>;
open cur_rows
fetch cur_rows into @ id, @ max
while @@ fetch_status = 0
begin
select @max = @ max -1
set rowcount @max
delete from table where the main field = @ id
fetch cur_rows into @ id, @ max
end
close cur_rows
set rowcount 0

Method two "duplicate records" There are two sense of duplicate records, one is completely repeated records, that is, all fields are repeated records, and second, some key fields duplicate records, such as the name field is repeated, and the other fields are not Must repeat or repeat can be ignored.

1, for the first repeat, easier to solve, use
select distinct * from tablename

You can get results without duplicate records set.
If the table needs to delete duplicate records (duplicate records reserved 1), can be deleted as follows
select distinct * into #tmp from tablename
drop table tablename
select * into tablename from #tmp
drop table #tmp

The reason for this duplication is the poor design of the table, increase the only index column can be solved.

2, such repetitive issues usually require the first record in the duplicate records, the operation is as follows Suppose there is a duplicate field name, address, require the only result set of these two fields
select identity (int, 1,1) as autoid, * into #tmp from tablename
select min (autoid) as autoid into # tmp2 from #tmp group by name, autoid
select * from #tmp where autoid in (select autoid from # tmp2)
The last one select that is the name, address not repeat the result set (but more than one autoid field, the actual write can be written in the select clause to save this column)

(four)
Query repeated
select * from tablename where id in (
select id from tablename
group by id
having count (id)> 1
)

Learning SQL for some time, I found a lot of duplicate records in the table I built to test (no index). Later summed up some methods to delete duplicate records, in oracle, you can delete duplicate records through the only rowid; You can also build a temporary table to achieve ... This only mentioned several of the simple and practical method, I hope everyone can Share (to table employee as an example).
sql> desc employee
name null? type
----------------------------------------- -------- - -----------------
emp_id number (10)
emp_name varchar2 (20)
salary number (10,2)
You can query for duplicate records with the following statement:
sql> select * from employee;
emp_id emp_name salary
---------- ---------------------------------------- ----------

1 sunshine 10000
1 sunshine 10000
2 semon 20000
2 semon 20000
3 xyz 30000
2 semon 20000
sql> select distinct * from employee;
emp_id emp_name salary
---------- ---------------------------------------- ----------
1 sunshine 10000
2 semon 20000
3 xyz 30000
sql> select * from employee group by emp_id, emp_name, salary having count (*)> 1
emp_id emp_name salary
---------- ---------------------------------------- ----------
1 sunshine 10000
2 semon 20000
sql> select * from employee e1
where rowid in (select max (rowid) from employe e2