WITH TMP AS ( SELECT 1 SEQ, 1 ORG_SEQ, 'A' AS CODE, 5 AS TOT, 1 AS NUM, '20190401' AS DTE FROM DUAL UNION ALL SELECT 2 SEQ, 1 ORG_SEQ, 'A' AS CODE, 5 AS TOT, 2 AS NUM, '20190402' AS DTE FROM DUAL UNION ALL SELECT 3 SEQ, 1 ORG_SEQ, 'A' AS CODE, 5 AS TOT, 3 AS NUM, '20190403' AS DTE FROM DUAL UNION ALL SELECT 4 SEQ, 1 ORG_SEQ, 'A' AS CODE, 5 AS TOT, 4 AS NUM, '20190404' AS DTE FROM DUAL UNION ALL SELECT 5 SEQ, 1 ORG_SEQ, 'A' AS CODE, 5 AS TOT, 5 AS NUM, '20190405' AS DTE FROM DUAL UNION ALL SELECT 6 SEQ, 6 ORG_SEQ, 'B' AS CODE, 2 AS TOT, 1 AS NUM, '20190401' AS DTE FROM DUAL UNION ALL SELECT 7 SEQ, 6 ORG_SEQ, 'B' AS CODE, 2 AS TOT, 2 AS NUM, '20190402' AS DTE FROM DUAL UNION ALL SELECT 8 SEQ, 8 ORG_SEQ, 'C' AS CODE, 1 AS TOT, 1 AS NUM, '20190401' AS DTE FROM DUAL UNION ALL SELECT 9 SEQ, 9 ORG_SEQ, 'C' AS CODE, 1 AS TOT, 1 AS NUM, '20190403' AS DTE FROM DUAL ) SELECT * FROM TMP; WHERE DTE = '20190401';
다음과 같이 데이타가 있을시 일자가 '20190401' 로 조회시 코드 A인 경우 TOT가 5 이므로 NUM이 5인 '20190405' 까지의 데이터를 더 가져오고,
B인 경우에는 TOT가 2 이므로 '20190402' 까지의 데이타를...C는 1이므로 '20190401 만 가져오고..
유일키는 SEQ이고 TOT가 풀어져서 원부모를 바라보게 되는키는 ORG_SEQ입니다.
이렇게 가져올수 있도록 쿼리가 가능할까요?
도움 부탁드립니다.
원하는 결과
CODE | TOT | NUM | DTE |
A | 5 | 1 | 20190401 |
A | 5 | 2 | 20190402 |
A | 5 | 3 | 20190403 |
A | 5 | 4 | 20190404 |
A | 5 | 5 | 20190405 |
B | 2 | 1 | 20190401 |
B | 2 | 2 | 20190402 |
C | 1 | 1 | 20190401 |