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)