该文提供了查找和删除数据库表中多个字段重复记录的方法。首先，使用SQL查询找出重复的料号和分类码，然后删除除每个组rowid最小的记录之外的所有重复项。这确保了每个组合只保留一条数据。
--查找表中多余的重复记录（多个字段）
select * from tmp a
where (a.料号,a.分类码) in (select 料号,分类码 from tmp group by 料号,分类码 having count(*) > 1)
--删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from tmp a
where (a.料号,a.分类码) in (select 料号,分类码 from tmp group by 料号,分类码 having count(*) > 1)
and rowid not in (select min(rowid) from tmp group by 料号,分类码 having count(*)>1)
--查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from tmp a
where (a.料号,a.分类码) in (select 料号,分类码 from tmp group by 料号,分类码 having count(*) > 1)
and rowid not in (select min(rowid) from tmp group by 料号,分类码 having count(*)>1)
扫一扫
548
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
