안녕하세요. 결과는 맞게 나오긴 하는데..효율적인지는..
with t as ( select 1 aa, 'a' bb from dual union all select 1, 'b' from dual union all select 2, 'a' from dual union all select 2, 'a' from dual union all select 3, 'b' from dual union all select 3, 'b' from dual union all select 4, 'a' from dual union all select 4, 'a' from dual union all select 4, 'b' from dual ) select distinct aa, bb from t where aa in ( select aa from t group by aa having count(distinct bb) > 1) order by aa, bb
with t as ( select 1 aa, 'a' bb from dual union all select 1, 'b' from dual union all select 2, 'a' from dual union all select 2, 'a' from dual union all select 3, 'b' from dual union all select 3, 'b' from dual union all select 4, 'a' from dual union all select 4, 'a' from dual union all select 4, 'b' from dual ) select aa, bb from (select distinct aa , bb , count(distinct bb) over(partition by aa) as gb from t where bb in ('a', 'b') ) where gb = 2 order by aa, bb ;