데이터베이스 질문과 답변
by 미들타이거 [SQL Query] #oracle #join #구간조회 [2023.12.19 15:29:23]
안녕하세요 고수님들~
sql문으로 A,B 두 테이블간에서 구간정보를 구하고 싶습니다. 가능할까요?ㅠㅠ
감사합니다.
A테이블
SELECT *
FROM
(
SELECT
2023-05-22' AS STRT_DT
, '2023-12-31' AS END_DT
365 AS YEAR_CNT from dual
UNION ALL
SELECT '2024-01-01' AS STRT_DT
2024-01-14' AS END_DT
366 AS YEAR_CNT FROM DUAL
) ;
B테이블
SELECT
FROM (
SELECT 2023-01-01' AS STRT_DT
, 2023-09-15' AS END_DT
, 1 AS RT FROM DUAL
UNION ALL
SELECT '2023-09-16' AS STRT_DT
, '2023-12-25' AS END_DT
, 3 AS RT FROM DUAL
UNION ALL
SELECT '2023-12-26" AS STRT_DT
, '9999-12-31' AS END_DT
, 5 AS RT From Dual
);
결과
STRT DT END_DT YEAR_CNT RT
2023-05-22 2023-09-15 365 1
2023-09-16 2023-12-25 365 3
2023-12-26 2023-12-31 365 5
2024-01-01 2024-01-14 366 5
WITH tab_a AS
(
SELECT '2023-05-22' strt_dt, '2023-12-31' end_dt, 365 year_cnt FROM dual
UNION ALL SELECT '2024-01-01', '2024-01-14', 366 FROM dual
)
, tab_b AS
(
SELECT '2023-01-01' strt_dt, '2023-09-15' end_dt, 1 rt FROM dual
UNION ALL SELECT '2023-09-16', '2023-12-25', 3 FROM dual
UNION ALL SELECT '2023-12-26', '9999-12-31', 5 FROM dual
)
SELECT GREATEST(a.strt_dt, b.strt_dt) strt_dt
, LEAST(a.end_dt, b.end_dt) end_dt
, a.year_cnt
, b.rt
FROM tab_a a
, tab_b b
WHERE a.strt_dt <= b.end_dt
AND b.strt_dt <= a.end_dt
;
정말 감사합니다. 큰도움이 됐습니다.^^
좋은 하루되세요~
) 버튼을 클릭하여 작성 하시면 됩니다.