oracle查詢重覆數據 select * from 表 where 條件 and 判重欄位 not in (select 判重欄位 from 表 where 條件 group by 判重欄位 having count(*) > 1) 根據rowid刪除重覆數據，保留一條 delete from 表 ...
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)
