利用Oracle的row_number() over函數消除重複的記錄

轉自：http://blog.csdn.net/sharpnessdotnet/archive/2009/06/24/4294549.aspx

 

1.select d.id,d.outer_code from  dict_depts_source d order by outer_code(查看重複資料)

2.select d.id,d.outer_code,row_number() over(partition by outer_code order by outer_code) row_flag from  dict_depts_source d(標識重複資料)

3.delete from dict_depts_source where id in(
select id from(select d.id,d.outer_code,row_number() over(partition by outer_code order by outer_code) row_flag from  dict_depts_source d)t
where t.row_flag > 1)(重複資料刪除資料)

3.select d.id,d.outer_code,row_number() over(partition by outer_code order by outer_code) row_flag from  dict_depts_source d(檢查刪除效果)

 

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/sharpnessdotnet/archive/2009/06/24/4294549.aspx