這篇文章給大家分享的是Oracle去除重復數(shù)據(jù)的方法，Oracle去重是比較實用的操作，文中示例代碼介紹的非常詳細，對大家學習Oracle數(shù)據(jù)庫的使用有一定的幫助，感興趣的朋友接下來一起跟隨小編看看吧。

    oracle去除重復數(shù)據(jù)的方法：

    創(chuàng)建測試數(shù)據(jù)

create table nayi224_180824(col_1 varchar2(10), col_2 varchar2(10), col_3 varchar2(10));
insert into nayi224_180824select 1, 2, 3 from dual 

union allselect 1, 2, 3 from dual 

union allselect 5, 2, 3 from dual 

union allselect 10, 20, 30 from dual ;

commit;select*from nayi224_180824;

    針對指定列，查出去重后的結(jié)果集

    distinct

select distinct t1.* from nayi224_180824 t1;

    方法局限性很大，因為它只能對全部查詢的列做去重。如果我想對col_2,col3去重，那我的結(jié)果集中就只能有col_2,col_3列，而不能有col_1列。

select distinct t1.col_2, col_3 from nayi224_180824 t1

    不過它也是最簡單易懂的寫法。

    row_number()

select *  from (select t1.*,
               row_number() over(partition by t1.col_2, t1.col_3 order by 1) rn          
               from nayi224_180824 t1) t1 where t1.rn = 1;

    寫法上要麻煩不少，但是有更大的靈活性。

    針對指定列，查出所有重復的行

    count having

select *  from nayi224_180824 t 
where (t.col_2, t.col_3) in (select t1.col_2, t1.col_3                                
from nayi224_180824 t1                               
group by t1.col_2, t1.col_3                              
having count(1) > 1)

    要查兩次表，效率會比較低。不推薦。

    count over

select *  from (select t1.*,               
count(1) over(partition by t1.col_2, t1.col_3) rn          
from nayi224_180824 t1) t1 where t1.rn > 1;

    只需要查一次表，推薦。

    刪除所有重復的行

delete from nayi224_180824 t where t.rowid in (                   
select rid                     
from (select t1.rowid rid,                                   
count(1) over(partition by t1.col_2, t1.col_3) rn                              
from nayi224_180824 t1) t1                    
where t1.rn > 1);

    就是上面的語句稍作修改。

    刪除重復數(shù)據(jù)并保留一條

    分析函數(shù)法

delete from nayi224_180824 t where t.rowid in (select rid                     
from (select t1.rowid rid,
    
    row_number() over(partition by t1.col_2, t1.col_3 order by 1) rn                             
    from nayi224_180824 t1) t1                    
    where t1.rn > 1);

    擁有分析函數(shù)一貫的靈活性高的特點?？梢詾樗麨榈姆纸M，并通過改變orderby從句來達到像”保留最大id“這樣的要求。

    group by

delete from nayi224_180824 t where t.rowid not in
       (select max(rowid) from nayi224_180824 t1 group by t1.col_2, t1.col_3);

    犧牲了一部分靈活性，換來了更高的效率。

    關于Oracle去重的方法及操作步驟就介紹到這，上述示例具有一定的借鑒價值，感興趣的朋友可以參考，希望能對大家有幫助。