oracle去除重复数据常用的方法

创建测试数据

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;

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11

COL_1     COL_2     COL_3
1     2     3
1     2     3
5     2     3
10     20     30
针对指定列，查出去重后的结果集
distinct

select distinct t1.* from nayi224_180824 t1;

    1

COL_1     COL_2     COL_3
10     20     30
1     2     3
5     2     3

方法局限性很大，因为它只能对全部查询的列做去重。如果我想对col_2,col3去重，那我的结果集中就只能有col_2,col_3列，而不能有col_1列。

select distinct t1.col_2, col_3 from nayi224_180824 t1

    1

COL_2     COL_3
2     3
20     30

不过它也是最简单易懂的写法。
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
;

    1
    2
    3
    4
    5
    6

COL_1     COL_2     COL_3     RN
1     2     3     1
10     20     30     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)

    1
    2
    3
    4
    5
    6

COL_1     COL_2     COL_3
1     2     3
1     2     3
5     2     3

要查两次表，效率会比较低。不推荐。
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
;

    1
    2
    3
    4
    5
    6

COL_1     COL_2     COL_3     RN
1     2     3     3
1     2     3     3
5     2     3     3

只需要查一次表，推荐。
删除所有重复的行

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

    1
    2
    3
    4
    5
    6
    7

就是上面的语句稍作修改。
删除重复数据并保留一条
分析函数法

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

    1
    2
    3
    4
    5
    6

拥有分析函数一贯的灵活性高的特点。可以为所欲为的分组，并通过改变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);

    1
    2
    3

牺牲了一部分灵活性，换来了更高的效率。
参考博文：https://blog.csdn.net/nayi_224/article/details/82020913