데이터베이스 질문과 답변
이 상태에서 BB 컬럼에 a, b 두가지 모두 있는 AA를 뽑고 싶은데 어떻게해야하는지 잘 모르겠네요...
| AA | BB |
| 1 | a |
| 1 | b |
| 2 | a |
| 2 | a |
| 3 | b |
| 3 | b |
| 4 | a |
| 4 | a |
| 4 | b |
결과출력
| AA | BB |
| 1 | a |
| 1 | b |
| 4 | a |
| 4 | b |
이런식으로 뽑으려고 하는데 어떤 방법이 있을까요...?
by pajama
[2020.10.24 23:15:19]
안녕하세요. 결과는 맞게 나오긴 하는데..효율적인지는..
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
by njnj
[2020.10.26 09:31:04]
와 그래도 해결은 했습니다 감사합니다!!
추가로 exists 구문으로 부분범위처리로도 가능한가요?
by 샤랄라
[2020.10.26 09:33:44]
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
;
by njnj
[2020.10.26 11:34:58]
감사합니다!!
추가로 exists 구문으로 부분범위처리로도 가능한가요?
) 버튼을 클릭하여 작성 하시면 됩니다.