用SQL语句,删除掉重复项只保留一条 在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢
1、查找表中多余的重复记录,重复记录是根据单个字段(trans_id)来判断
select *
from intf_task_instance t
where t.trans_id in (select trans_id
from intf_task_instance
group by (trans_id, intf_task_instance_id)
having count(trans_id) > 1);
2、删除表中多余的重复记录,重复记录是根据单个字段(intf_task_instance_id)来判断,只留有rowid最小的记录
delete from intf_task_instance t
where t.intf_task_instance_id in
(select intf_task_instance_id
from intf_task_instance
group by intf_task_instance_id
having count(intf_task_instance_id) > 1)
and rowid not in (select min(Rowid)
from intf_task_instance
group by intf_task_instance_id
having count(intf_task_instance_id) > 1);
3、查找表中多余的重复记录(多个字段)
select *
from vitae a
where (a.peopleId, a.seq) in (select peopleId, seq
from vitae
group by peopleId, seq
having count(*) > 1)
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId, a.seq) in (select peopleId, seq
from vitae
group by peopleId, seq
having count(*) > 1)
and rowid not in (select min(rowid)
from vitae
group by peopleId, seq
having count(*) > 1)
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select *
from vitae a
where (a.peopleId, a.seq) in (select peopleId, seq
from vitae
group by peopleId, seq
having count(*) > 1)
and rowid not in (select min(rowid)
from vitae
group by peopleId, seq
having count(*) > 1)