데이터베이스 질문과 답변
일전에 "데이터에 쌍으로 존재하는 데이터만 추출하는 쿼리를 여쭈어봅니다." 라는 글로 여쭈어 봐서 해결을 했는데 좀 더 업그레이드 된 버전이
나오니 해결방법을 못 찾고 다시 여쭙니다.
WITH t AS ( SELECT 1 seq, 'A' AS a, '한국' AS b, null AS c FROM DUAL UNION ALL SELECT 2 seq, 'A' AS a, null AS b, '우' AS c FROM DUAL UNION ALL SELECT 3 seq, 'A' AS a, null AS b, '우' AS c FROM DUAL UNION ALL SELECT 4 seq, 'A' AS a, '한국' AS b, null AS c FROM DUAL UNION ALL SELECT 5 seq, 'B' AS a, null AS b, '우' AS c FROM DUAL UNION ALL SELECT 6 seq, 'B' AS a, null AS b, '우' AS c FROM DUAL UNION ALL SELECT 7 seq, 'C' AS a, '한국' AS b, null AS c FROM DUAL UNION ALL SELECT 8 seq, 'C' AS a, null AS b, '우' AS c FROM DUAL UNION ALL SELECT 9 seq, 'C' AS a, null AS b, '우' AS c FROM DUAL UNION ALL SELECT 10 seq, 'D' AS a, '한국' AS b, null AS c FROM DUAL UNION ALL SELECT 11 seq, 'E' AS a, '한국' AS b, null AS c FROM DUAL UNION ALL SELECT 12 seq, 'E' AS a, '한국' AS b, null AS c FROM DUAL UNION ALL SELECT 13 seq, 'E' AS a, '한국' AS b, null AS c FROM DUAL )
이 때 조건으로(b = '한국' OR c = '우')가 입력 됩니다.
b, c 필드의 값이 쌍으로 이루어지면서 여기에 a 필드 값이 같아야 합니다.
예상결과값은
seq a b c ==================================== 1 'A' '한국' 2 'A' '우' 3 'A' '우' 4 'A' '한국' 7 'C' '한국' 8 'C' '우'
입니다.
해결 쿼리 부탁드립니다.
감사합니다.
by 마농
[2021.05.03 08:02:35]
WITH t AS
(
SELECT 1 seq, 'A' a, '한국' b, null c FROM dual
UNION ALL SELECT 2, 'A', null , '우' FROM dual
UNION ALL SELECT 3, 'A', null , '우' FROM dual
UNION ALL SELECT 4, 'A', '한국', null FROM dual
UNION ALL SELECT 5, 'B', null , '우' FROM dual
UNION ALL SELECT 6, 'B', null , '우' FROM dual
UNION ALL SELECT 7, 'C', '한국', null FROM dual
UNION ALL SELECT 8, 'C', null , '우' FROM dual
UNION ALL SELECT 9, 'C', null , '우' FROM dual
UNION ALL SELECT 10, 'D', '한국', null FROM dual
UNION ALL SELECT 11, 'E', '한국', null FROM dual
UNION ALL SELECT 12, 'E', '한국', null FROM dual
UNION ALL SELECT 13, 'E', '한국', null FROM dual
)
SELECT seq, a, b, c
FROM (SELECT seq, a, b, c
, COUNT(*) OVER(PARTITION BY a, rn) cnt
FROM (SELECT seq, a, b, c
, ROW_NUMBER() OVER(PARTITION BY a, b, c ORDER BY seq) rn
FROM t
WHERE (b = '한국' OR c = '우')
)
)
WHERE cnt = 2
ORDER BY seq
;
by 짱구
[2021.05.03 09:09:41]
마농님께 항상 많은걸 배워갑니다.
정말 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.