oracle查詢重複資料

標籤：acl   row   查詢   資料   bsp   org   欄位   重複數   重複   

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)

 

oracle查詢重複資料