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;

select distinct t1.* from nayi224_180824 t1;

select distinct t1.col_2, col_3 from nayi224_180824 t1

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

;

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)

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

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

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