Oracle重复行查询实例教程

oracle重复行查询实例教程

create table dlp_test (a number(2),b number(2));

 insert into dlp_test values(1,2);
 insert into dlp_test values(1,2);
 insert into dlp_test values(3,4);
 insert into dlp_test values(5,6);
 insert into dlp_test values(3,5); 
 insert into dlp_test values(3,5);

select * from  dlp_test ;

--获取不重复行
select distinct x.* from dlp_test x;

--获取仅出现一次的行
--①
select a.*
  from dlp_test a,
       (select a, b from dlp_test group by a, b having count(*) = 1) b
 where a.a = b.a
   and a.b = b.b;
--②
select *
  from dlp_test
 where rowid in (select rowid
                   from (select a, b, count(*) over(partition by a, b) t
                           from dlp_test)
                  where t = 1);