테이블에 내역을 조회했을때
row 1 -- > '20160908' , '20160909'
row 2 -- > '20160921' , '20160923'
이렇게 나왔을때(row 는 v개가 나올수 있음)
row 1,row2 각각 두날짜 사의이 일자를 행으로 표현하려면 어떻게 해야 할까요?
row 1 -- > 20160908
20160909
row2 -- > 20160921
20160922
20160923
row 가 한개일때는
SELECT sdt + (LEVEL-1)
FROM (SELECT TO_DATE('20160921', 'yyyymmDD') sdt,
TO_DATE('20160923', 'yyyymmDD') edt
FROM dual) CONNECT BY LEVEL <= EDT+1 -SDT
이렇게 하면 되겠는데 row가 여러건 나올때는 어떻게 해야 할까요?
SELECT DISTINCT SDT + (LV - 1) DT FROM (SELECT TO_DATE('20160921', 'yyyymmDD') SDT, TO_DATE('20160925', 'yyyymmDD') EDT FROM DUAL UNION ALL SELECT TO_DATE('20160927', 'yyyymmDD') SDT, TO_DATE('20161003', 'yyyymmDD') EDT FROM DUAL) , (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL < 10000) WHERE LV <= EDT + 1 - SDT ORDER BY DT
WITH T AS ( SELECT TO_DATE('20160908', 'yyyymmDD') sdt, TO_DATE('20160909', 'yyyymmDD') edt FROM dual UNION ALL SELECT TO_DATE('20160921', 'yyyymmDD') sdt, TO_DATE('20160923', 'yyyymmDD') edt FROM dual ) SELECT rn, MIN(sdt) OVER(PARTITION BY rn ORDER BY lv) + lv -1 dt FROM (SELECT T.*, rownum rn FROM T), (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 1000) WHERE lv <= edt - sdt + 1 ORDER BY rn, lv
점수용..