不考虑效率问题 这个应该可以了 with t as ( select 1 c1,'a' c2 from dual union all select 1 c1,'b' c2 from dual union all select 2 c1,'a' c2 from dual union all select 2 c1,'b' c2 from dual union all select 2 c1,'c' c2 from dual ) select * from t where c1 = (select c1 from t group by c1 having count(c1) = 3 ) c1 c2 ---------------------- 1 2 a 2 2 b 3 2 c
with t as
(
select 1 c1,'a' c2 from dual union all
select 1 c1,'b' c2 from dual union all
select 2 c1,'a' c2 from dual union all
select 2 c1,'b' c2 from dual union all
select 2 c1,'c' c2 from dual
)
select * from t
where c1 = (select c1
from t
group by c1
having count(c1) = 3
)
c1 c2
----------------------
1 2 a
2 2 b
3 2 c