create  table ttt
         (
          id number(4) primary key,
          name varchar2(16) ,
          sex varchar2(2),
          age number(4)
         )
         
         insert into ttt values (1, 'a','男', 2);
         insert into ttt values (6, 'a','男', 4);
         insert into ttt values (3, 'a','男', 3);
         insert into ttt values (8, 'a','女', 4);
         
         insert into ttt values (7, 'b','女', 4);
         insert into ttt values (2, 'b','女', 2);
         insert into ttt values (9, 'b','女', 4);
         
         insert into ttt values (4, 'c','女', 4);
         insert into ttt values (5, 'e','女', 5);

--这个算法最简单，但是真实数据量很大，这个效率太差
select count(distinct t.id) from ttt t,
         (select name,0 age,sex from ttt group by name,sex having count(1)>1 union
         select name,age,'' sex from ttt group by name,age having count(1)>1) m where 
         t.name=m.name and (t.age=m.age or t.sex=m.sex);

--此种方式不能去除重复记录，查出来为10条
select sum(count(1)) coun from ttt group by grouping sets((name,sex),(name,age)) having count(1)>1;

with a as
   (select id, count(*) over(partition by t.name, t.sex) count from ttt t),
  b as
   (select id, count(*) over(partition by t.name, t.age) count from ttt t)
  select count(id)
    from (select a.id
            from a
           where a.count >= 2
          union
          select b.id from b where b.count >= 2)

------解决方案--------------------

在原来基础上还可以继续优化，代码如下：
SQL code
select count(*)
  from (select id,
               count(*) over(partition by t.name, t.sex) count1,
               count(*) over(partition by t.name, t.age) count2
          from ttt t) a
 where a.count1 > 1
    or a.count2 > 1

------解决方案--------------------

探讨


引用:

在原来基础上还可以继续优化，代码如下：
SQL code
select count(*)