[Oracle] 오라클 고급 JOIN 테크닉 - 선분이력 끊기
선분이력 끊기
→ 월도와 선분이력을 합하여 변환된 선분이력의 형태를 구해야 함
어떤식으로 JOIN을 해야될지 매우 헷갈린다;; 그럴땐.. Let's 실습!!
CREATE TABLE MINA.TMONTH (MON, SDATE, EDATE) AS
SELECT '2009/06', '2009/06/01', '2009/06/30' FROM DUAL
UNION ALL
SELECT '2009/07', '2009/07/01', '2009/07/31' FROM DUAL
UNION ALL
SELECT '2009/08', '2009/08/01', '2009/08/31' FROM DUAL
UNION ALL
SELECT '2009/09', '2009/09/01', '2009/09/30' FROM DUAL
UNION ALL
SELECT '2009/10', '2009/10/01', '2009/10/31' FROM DUAL;
CREATE TABLE MINA.TSEGMENT (GOODS_CODE, SDATE, EDATE, ETC) AS
SELECT 'A', '2009/07/13', '2009/08/08', 'A1' FROM DUAL
UNION ALL
SELECT 'A', '2009/08/09', '2009/08/20', 'A2' FROM DUAL
UNION ALL
SELECT 'A', '2009/08/21', '2009/10/07', 'A3' FROM DUAL;
- TMONTH
- TSEGMENT
두 테이블을 JOIN하여 변환된 선분이력으로 만들어보자.
SELECT A.MON, B.SDATE, B.EDATE, A.SDATE, A.EDATE, B.ETC
FROM MINA.TMONTH A, MINA.TSEGMENT B
WHERE B.SDATE <= A.EDATE
AND B.EDATE >= A.SDATE;
▶ 시작일자는 큰 값, 종료일자는 작은 값으로 SELECT 하면 변환된 선분이력의 형태가 나옴
SELECT B.GOODS_CODE GOODS,
GREATEST(A.SDATE, B.SDATE) SDATE,
LEAST(A.EDATE, B.EDATE) EDATE,
B.ETC
FROM MINA.TMONTH A, MINA.TSEGMENT B
WHERE B.SDATE <= A.EDATE
AND B.EDATE >= A.SDATE;
