co1 | col2 | col3
1 | A | 2016-02-01
1 | B | 2016-02-02
1 | A | 2016-02-03
2 | A | 2016-02-04
2 | B | 2016-02-05
2 | B | 2016-02-06
3 | A | 2016-02-07
3 | B | 2016-02-08
3 | C | 2016-02-09
위와 같은 데이터가 있다고 했을 때
col1을 기준으로 col2의 A,B,C 값이 모두 있는 col1값만 가져오는 방법이 어떻게 될까요
결과값
col1
3
4
.
.
.
위와 같이 나왔으면 좋겠습니다.
감사합니다.
Comment 2
-
Terry
2017.06.12 21:58
-
知音
2017.06.21 13:57
참고하세요
with tbla (col1,col2,col3) as
(select '1','A','2016-02-01' union allselect '1','B','2016-02-02' union allselect '1','A','2016-02-03' union allselect '2','A','2016-02-04' union allselect '2','B','2016-02-05' union allselect '2','B','2016-02-06' union allselect '3','A','2016-02-07' union allselect '3','B','2016-02-08' union allselect '3','C','2016-02-09')select col1from (select col1, count(distinct case when col2 = 'A' then 1 end)+ count(distinct case when col2 = 'B' then 1 end)+ count(distinct case when col2 = 'C' then 1 end) valfrom tblagroup by col1) tblbwhere val = 3
Group By..Union All.. Having 이용
;with tblA(col1,col2,col3) As
(
Select '1','A','2016-02-01' Union All
Select '1','B','2016-02-02' Union All
Select '1','A','2016-02-03' Union All
Select '2','A','2016-02-04' Union All
Select '2','B','2016-02-05' Union All
Select '2','B','2016-02-06' Union All
Select '3','A','2016-02-07' Union All
Select '3','B','2016-02-08' Union All
Select '3','C','2016-02-09'
)
Select a.col1
From (
Select 1 As cnt
,a.col1
From tblA a
Where a.col2 = 'A'
Group By a.col1
Union All
Select 1 As cnt
,a.col1
From tblA a
Where a.col2 = 'B'
Group By a.col1
Union All
Select 1 As cnt
,a.col1
From tblA a
Where a.col2 = 'C'
Group By a.col1
) a
Group By a.col1
Having Sum(a.cnt) > 2