删除数据库表中的重复数据

代码

    delete from tbl where (id) in(    select id from (    select id from tbl where (name,age) in         (        select name,age from tbl group by name,age  having count(*)>1        )        and id not in (        select min(id) from tbl group by name,age having count(*)>1        )    )tbl)

详解

• 1

select name,age from tbl group by name,age  having count(*)>1

搜索名字和年龄相同的数据

• 2

select min(id) from tbl group by name,age having count(*)>1

搜索出名字和年龄相同的数据中id最小的列

• 3

select id from tbl where (name,age) in         (        select name,age from tbl group by name,age  having count(*)>1        )        and id not in (        select min(id) from tbl group by name,age having count(*)>1        )

将第一句sql和第二句sql合并 not in 表示排除，
产生一个新的所有符合标准的id所有值

• 4

select id from (    select id from tbl where (name,age) in         (        select name,age from tbl group by name,age  having count(*)>1        )        and id not in (        select min(id) from tbl group by name,age having count(*)>1        )    )tbl

由于在mysql中，必须要在外部再包含一个sql 所以这个不能少

• 5

delete from tbl where (id) in(    select id from (    select id from tbl where (name,age) in         (        select name,age from tbl group by name,age  having count(*)>1        )        and id not in (        select min(id) from tbl group by name,age having count(*)>1        )    )tbl)

最后 所有符合标准的id 全部删除即可