oracle中查询重复的数据,oracle查询重复数据

oracle查询重复数据

select * from 表 where 条件 and 判重字段 not in

(select 判重字段 from 表 where 条件 group by 判重字段 having count(*) > 1)

根据rowid删除重复数据，保留一条

delete from 表 where 条件 and 判重字段 not in

(select 判重字段 from 表 where 条件 group by 判重字段 having count(*) >1)

and rowid not in

(select max(rowid) from 表 where 条件 group by 判重字段 having count(*) >1)

eg：删除导入病案数据中的重复数据

delete from rhsa_hs4_1_2013_temp

where period = '00083'

and org_id = '370000003584'

and (bah, zycs) in (select bah, zycs

from rhsa_hs4_1_2013_temp

where period = '00083'

and org_id = '370000003584'

group by bah, zycs

having count(*) > 1)

and rowid not in (select max(rowid)

from rhsa_hs4_1_2013_temp

where period = '00083'

and org_id = '370000003584'

group by bah, zycs

having count(*) > 1)

内容来源于网络如有侵权请私信删除