当一某个字段重复判断重复时 :查询重复数据 重复取id最小值
select table,min(id)
from table
where “字段” = 'xx'
group by “重复字段” having count(1) > 1 ;
查询影响条数
select * from table
where " 字段"= 'xx'
and id not in
(select min(id)
from table
where "字段" = 'xx'
group by "重复字段" having count(1) > 1 )
and "重复字段" in
(select "重复字段"
from table
where "字段" = 'xx'
group by "重复字段" having count(1) > 1 );
删除
delete from table
where "字段" = 'xx'
and id not in (select a.* from
(select min(id)
from table
where "字段" = 'xx'
group by 重复字段 having count(1) > 1 ) a )
and "重复字段" in (select b.* from
(select "重复字段"
from table
where "字段" = 'xx'
group by "重复字段" having count(1) > 1 ) b);
所有字段重复时:使用 distinct
insert into table1 select distinct * from table ;
ALTER TABLE table RENAME TO table_20220908;
ALTER TABLE table1RENAME TO table ;