当去掉一个字段的重复时
查找重复
select 字段 from 表名称 group by 字段 having count(1)>1
删掉重复 只保最小的一行
delete from 表名称 where 字段 in (select 字段 from 表名称 group by 字段 having count(1)>1) and rowid not in (select min(rowid) from 表名称 group by 字段 having count(1)>1)
去掉多个字段的重复字段
查找多个字段的重复
select source_code,t.companyname,count(1) from HP_FIN_MAP_ENTITY t group by source_code,t.companyname having count(1)>1
去掉重复的(注意 里面的字段要互相对应,不能使顺序倒置)
delete from HP_FIN_MAP_ENTITY t
where (t.source_code,t.companyname) in (select source_code,t.companyname from HP_FIN_MAP_ENTITY t group by source_code,t.companyname having count(1)>1 )
and rowid not in (select min(rowid) from HP_FIN_MAP_ENTITY group by source_code,t.companyname having count(1)>1 )
注:delete 与truncate
1、删除表中所有数据,但保留表结构(可用以下两个语句):
2、删除表中部分数据:
3、删除表结构及数据: