在我看来，自我加入会有所帮助。
样本数据：

SQL> with doctor (name, hospital, dept) as
  2    (select 'Rajib'  , 'A' , 'EYE'   from dual union all
  3     select 'Raj'    , null, null    from dual union all
  4     select 'Sumit'  , 'B' , 'BRAIN' from dual union all
  5     select 'Saurabh', 'A' , 'NOSE'  from dual union all
  6     select 'Deepak' , 'C' , 'EYE'   from dual
  7    )

查询方式：

8  select a.name
  9  from doctor a join doctor b on a.hospital = b.hospital
 10                             and a.dept <> b.dept
 11                             and a.name <> b.name;

NAME
-------
Saurabh
Rajib

SQL>

select 
  doctor,
  hospital,
  count(distinct dept) 
from ...
group by doctor,
  hospital

将给予每个医生/医院组合的不同科室的数量。
从那里，您可以使用内联视图或HAVING子句来任意限制结果