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