A테이블 a의날짜 컬럼이 있고 b,c,d,e 컬럼에 임의의 값이 있는 테이블이 있습니다.
a | b | c | d | e | f |
2/1 | A | 1 | 1 | 1 | 1 |
2/1 | B | 2 | 2 | 2 | 2 |
2/4 | F | 3 | 3 | 3 | 3 |
2/10 | B | 50 | 50 | 50 | 50 |
2/10 | C | 60 | 60 | 60 | 60 |
결과. 일자가 A테이블 일자보다 작거나 같은 일자의 값을 셋팅하는 결과가 나오도록 하는데 어렵네요;;
일자 | a | b | c | d | e | f |
2/1 | 2/1 | A | 1 | 1 | 1 | 1 |
2/1 | 2/1 | B | 2 | 2 | 2 | 2 |
2/2 | 2/1 | A | 1 | 1 | 1 | 1 |
2/2 | 2/1 | B | 2 | 2 | 2 | 2 |
2/3 | 2/1 | A | 1 | 1 | 1 | 1 |
2/3 | 2/1 | B | 2 | 2 | 2 | 2 |
2/4 | 2/4 | F | 3 | 3 | 3 | 3 |
2/5 | 2/4 | F | 3 | 3 | 3 | 3 |
2/6 | 2/4 | F | 3 | 3 | 3 | 3 |
2/7 | 2/4 | F | 3 | 3 | 3 | 3 |
2/8 | 2/4 | F | 3 | 3 | 3 | 3 |
2/9 | 2/4 | F | 3 | 3 | 3 | 3 |
2/10 | 2/10 | B | 50 | 50 | 50 | 50 |
2/10 | 2/10 | C | 60 | 60 | 60 | 60 |
2/11 | 2/10 | B | 50 | 50 | 50 | 50 |
2/11 | 2/10 | C | 60 | 60 | 60 | 60 |
2/12 | 2/10 | B | 50 | 50 | 50 | 50 |
2/12 | 2/10 | C | 60 | 60 | 60 | 60 |
2/13 | 2/10 | B | 50 | 50 | 50 | 50 |
2/13 | 2/10 | C | 60 | 60 | 60 | 60 |
2/14 | 2/10 | B | 50 | 50 | 50 | 50 |
2/14 | 2/10 | C | 60 | 60 | 60 | 60 |
고수님들 도와주세요~~
WITH t AS ( SELECT '20200201' a, 1 b, 1 c, 1 d, 1 e FROM dual UNION ALL SELECT '20200204', 3, 3, 3, 3 FROM dual UNION ALL SELECT '20200210', 5, 5, 5, 5 FROM dual ) SELECT * FROM (SELECT dt , LAST_VALUE(a) IGNORE NULLS OVER(ORDER BY dt) a , LAST_VALUE(b) IGNORE NULLS OVER(ORDER BY dt) b , LAST_VALUE(c) IGNORE NULLS OVER(ORDER BY dt) c , LAST_VALUE(d) IGNORE NULLS OVER(ORDER BY dt) d , LAST_VALUE(e) IGNORE NULLS OVER(ORDER BY dt) e FROM (SELECT TO_CHAR(s + LEVEL - 1, 'yyyymmdd') dt FROM (SELECT TO_DATE(MAX(a), 'yyyymmdd') s , TO_DATE(:edt , 'yyyymmdd') e FROM t WHERE a <= :sdt ) CONNECT BY LEVEL <= e - s + 1 ) a , t b WHERE a.dt = b.a(+) ) WHERE dt >= :sdt ;