查询出所有重复记录 1 2 3 | select 字段名,字段名 from 表名 where 重复字段名 in (select 重复字段名
from 表名 group by 重复字段名 having count(1) >= 2)
ORDER BY 重复字段名 |
查询出所有重复记录并且删除多余的只保留一条 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | delete from 表名
where
重复字段名 in (
SELECT a.重复字段名from(
select 重复字段名
from 表名
group by 重复字段名 having count(1) > 1
) a
)
and
id(只保留id最小的一个) not in (
SELECT b.id from(
select min(id) as id
from 表名
group by 重复字段名 having count(1)>1
) b
)
|
另一种删除方法：
delete from users where id not in ( select t.max_id from (select max(id) as max_id from users group by name) as t ); |