1:1 1:N N:1 확인 쿼리 문의
1번경우
1 2
1 3
2번 경우
2 1
3 1
3번 경우
3 4
위처럼 칼럼 두개를 비교해서 관계를 나타내려 하는데요
1번경우는 1:N 2번경우는 N:1 3번은 1:1 이렇게요.
쿼리를 어떻게 짜야하나요?
WITH t AS ( SELECT 1 gb, 1 c1, 2 c2 FROM dual UNION ALL SELECT 1, 1, 3 FROM dual UNION ALL SELECT 2, 2, 1 FROM dual UNION ALL SELECT 2, 3, 1 FROM dual UNION ALL SELECT 3, 1, 2 FROM dual UNION ALL SELECT 3, 3, 4 FROM dual UNION ALL SELECT 4, 1, 3 FROM dual UNION ALL SELECT 4, 1, 4 FROM dual UNION ALL SELECT 4, 2, 4 FROM dual ) SELECT gb , DECODE(cnt, cnt_1, '1', 'N') || ':' || DECODE(cnt, cnt_2, '1', 'N') AS relation FROM (SELECT gb , COUNT(*) cnt , COUNT(DISTINCT c1) cnt_1 , COUNT(DISTINCT c2) cnt_2 FROM t GROUP BY gb ORDER BY gb ) ;
with t as ( select 1 gb, 1 c1, 2 c2 from dual union all select 1, 1, 3 from dual union all select 2, 2, 1 from dual union all select 2, 3, 1 from dual union all select 3, 1, 2 from dual union all select 3, 3, 4 from dual union all select 4, 1, 3 from dual union all select 4, 1, 4 from dual union all select 4, 2, 4 from dual union all select 5, 1, 3 from dual union all select 5, 1, 5 from dual union all select 5, 1, 7 from dual union all select 5, 1, 2 from dual union all select 5, 1, 9 from dual union all select 5, 1, 4 from dual ) select gb ,decode(cnt,decode(cnt_1 ,cnt_2,cnt_1 ,cnt_2) ,'1','N' ) || ':' || decode(cnt,decode(cnt_2 ,cnt_1,cnt_2 ,cnt_1) ,'1','N' ) AS relation from ( select gb ,count(*) cnt ,count(distinct c1) cnt_1 ,count(distinct c2) cnt_2 from t group by gb order by gb ) ;
