oracle根据字段去查询表重复数据

Natalia_Portman

已于 2023-10-11 09:15:59 修改

阅读量158

点赞数

文章标签： oracle 数据库

于 2023-10-10 17:56:10 首次发布

本文链接：https://blog.csdn.net/Natalia_Portman/article/details/133753052

版权

文章介绍了在数据库中使用SQL语句进行数据清洗，包括通过`fid`和`fpro_code`字段的分组计数来找出重复项，并使用条件删除非唯一行的操作。展示了针对不同表结构（如`t_ams_product`和`t_rp_org`）的示例。

摘要由CSDN通过智能技术生成

单字段
1、查询
select * from t_ams_product where fid in (select fid from t_ams_product group by fid having count(fid) > 1)

2、删除
根据id
delete from t_ams_product where (fid) in ( select fid from t_ams_product group by fid having count(fid) > 1) and rowid not in (select min(rowid) from t_ams_product group by fid having count(*) > 1);

根据其他字段
delete from t_ams_product where fpro_code in (select fpro_code from t_ams_product group by fpro_code having count (fpro_code) > 1 ) and fid not in (select min(fid) from t_ams_product group by fpro_code having count(fpro_code) > 1 )

多字段
删除
delete from t_rp_org a where (a.forg_type ,a.forg_name) in (select forg_type ,forg_name from t_rp_org group by forg_type ,forg_name having count() > 1) and rowid not in (select min(rowid) from t_rp_org group by forg_type ,forg_name having count()>1)