本篇文章給大家?guī)?lái)了關(guān)于Oracle的相關(guān)知識(shí)，其中主要介紹了關(guān)于數(shù)據(jù)清理的時(shí)候常常會(huì)清除表中的重復(fù)的數(shù)據(jù),那么在oracle中怎么處理呢？下面一起來(lái)看一下，希望對(duì)大家有幫助。

推薦教程：《Oracle視頻教程》

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

create table nayi224_180824(col_1 varchar2(10), col_2 varchar2(10), col_3 varchar2(10)); insert into nayi224_180824 select 1, 2, 3 from dual union all select 1, 2, 3 from dual union all select 5, 2, 3 from dual union all select 10, 20, 30 from dual ; commit; select*from nayi224_180824;

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

distinct

select distinct t1.* from nayi224_180824 t1;

方法局限性很大，因?yàn)樗荒軐?duì)全部查詢的列做去重。如果我想對(duì)col_2,col3去重，那我的結(jié)果集中就只能有col_2,col_3列，而不能有col_1列。

select distinct t1.col_2, col_3 from nayi224_180824 t1

不過(guò)它也是最簡(jiǎn)單易懂的寫(xiě)法。

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 ;

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

針對(duì)指定列，查出所有重復(fù)的行

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)

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

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 ;

只需要查一次表，推薦。

刪除所有重復(fù)的行

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);

就是上面的語(yǔ)句稍作修改。

刪除重復(fù)數(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ù)一貫的靈活性高的特點(diǎn)?？梢詾樗麨榈姆纸M，并通過(guò)改變orderby從句來(lái)達(dá)到像”保留最大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);

犧牲了一部分靈活性，換來(lái)了更高的效率。

推薦教程：《Oracle視頻教程》