ORACLE 去除重复记录的方法
select count (*) from a where name in ( --统计A表中 name不重复的个数
select name from a group by name having count(*)=1 -- 查询出不重复的table A的 name字段值
);
select name from a group by name having count(*)=1 -- 查询出不重复的table A的 name字段值
);
-- 查询表 a 的 name字段重复的记录
select *
from a
where a.rowid > (select min(x.rowid) from a x where x.name = a.name);
create table t_person( cardid integer primary key, pname varchar2(10), address varchar2(20) ) tablespace cici;
表结构建立
插入如下数据
select rowid ,p.* from t_person p order by cardid asc
- 子查询 查询重复的记录
- 查询单个字段(cardid)重复的记录
--定位查询 select * from t_person where PNAME in --获取重复记录的pname属性 (select PNAME from t_person group by PNAME having count(PNAME) > 1)
- 查询多个字段来(cardid,pname)重复的记录
select * from t_person a where (a.pname, a.ADDRESS) in (select pname, ADDRESS from t_person group by pname, ADDRESS having count(*) > 1)
--不包含rowid最小的记录
select * from t_person a where (a.pname, a.ADDRESS) in (select pname, ADDRESS from t_person group by pname, ADDRESS having count(*) > 1) and rowid not in (select min(rowid) from t_person group by cardid,ADDRESS having count(*)>1)
- 查询 非重复的记录
- pname不重复的数据记录
select * from t_person where PNAME not in --获取重复记录的pname属性 (select PNAME from t_person group by PNAME having count(PNAME) > 1)
- address不重复的数据
select * from t_person where address not in --获取重复记录的pname属性 (select address from t_person group by address having count(address) > 1) order by cardid asc
- 查询pname 和 address都不重复的数据
select * from t_person where PNAME not in --获取重复记录的pname属性 (select PNAME from t_person group by PNAME having count(PNAME) > 1) intersect /*求交集*/ select * from t_person where address not in --获取重复记录的pname属性 (select address from t_person group by address having count(address) > 1 )
4. 删除 子查询查找出的重复记录数目
- 查询多个字段来(cardid,pname)重复的记录
--不包含rowid最小的记录
delete from t_person where cardid in ( select cardid from t_person a where (a.pname, a.ADDRESS) in (select pname, ADDRESS from t_person group by pname, ADDRESS having count(*) > 1) and rowid not in (select min(rowid) from t_person group by cardid,ADDRESS having count(*)>1))