目錄

• 創(chuàng)建測(cè)試數(shù)據(jù)
• 針對(duì)指定列，查出去重后的結(jié)果集
• distinct
• row_number()
• 針對(duì)指定列，查出所有重復(fù)的行
• count having
• count over
• 刪除所有重復(fù)的行
• 刪除重復(fù)數(shù)據(jù)并保留一條
• 分析函數(shù)法
• group by
• 總結(jié)

創(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;

COL_1COL_2COL_3123123523102030

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

distinct

select distinct t1.* from nayi224_180824 t1;

COL_1COL_2COL_3102030123523

方法局限性很大，因?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

COL_2COL_3232030

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

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
;

COL_1COL_2COL_3RN12311020301

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

針對(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)

COL_1COL_2COL_3123123523

要查兩次表，效率會(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
;

COL_1COL_2COL_3RN123312335233

只需要查一次表，推薦。

刪除所有重復(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);

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

刪除重復(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)。可以為所欲為的分組，并通過改變orderby從句來達(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);

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

總結(jié)

到此這篇關(guān)于oracle數(shù)據(jù)庫去除重復(fù)數(shù)據(jù)常用的文章就介紹到這了,更多相關(guān)oracle去除重復(fù)數(shù)據(jù)內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！