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

代码

    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 全部删除即可