데이터베이스 질문과 답변
오라클 데이터 추출 질문드립니다
2
521
by 제리1 [2025.06.17 11:42:09]
오라클이고 일자 컬럼은 Varchar2 입니다
기존 데이터는
| 구분 | 일자 |
| Y | 20240515 |
| Y | 20250516 |
| N | 20250517 |
| N | 20250518 |
| Y | 20250519 |
| Y | 20250520 |
원하는 데이터
| 구분 | 일자 |
| Y | 20250514 |
| Y | 20250514 |
| N | 20250516 |
| N | 20250517 |
| Y | 20250518 |
| Y | 20250518 |
감사합니다
by 마농
[2025.06.17 18:03:29]
WITH t AS
(
SELECT 'Y' gb, '20250515' dt FROM dual
UNION ALL SELECT 'Y', '20250516' FROM dual
UNION ALL SELECT 'N', '20250517' FROM dual
UNION ALL SELECT 'N', '20250518' FROM dual
UNION ALL SELECT 'Y', '20250519' FROM dual
UNION ALL SELECT 'Y', '20250520' FROM dual
)
SELECT CONNECT_BY_ROOT(gb) gb
, CONNECT_BY_ROOT(dt) dt
, TO_CHAR(TO_DATE(dt, 'yyyymmdd') - 1, 'yyyymmdd') dt_
FROM t
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR gb = 'Y'
AND gb = 'Y'
AND TO_CHAR(TO_DATE(dt, 'yyyymmdd') + 1, 'yyyymmdd') = PRIOR dt
;