根据一条数据中的开始时间和结束时间将数据拆成多条数据
WITH TEMPTABLE AS(
SELECT
'1' TEMPID,
TO_DATE('2019-01-01','YYYY-MM-DD') STARTTIME,
TO_DATE('2019-01-05','YYYY-MM-DD') ENDTIME
FROM DUAL
UNION ALL
SELECT
'2' TEMPID,
TO_DATE('2019-01-11','YYYY-MM-DD') STARTTIME,
TO_DATE('2019-01-13','YYYY-MM-DD') ENDTIME
FROM DUAL
)
SELECT
TEMPID,
STARTTIME,
ENDTIME,
TRUNC(STARTTIME) + LEVEL - 1 AS DATELABEL
FROM
TEMPTABLE A
CONNECT BY
LEVEL < TO_NUMBER(TRUNC(ENDTIME)- TRUNC(STARTTIME)) + 2
AND PRIOR TEMPID = TEMPID
AND PRIOR DBMS_RANDOM.RANDOM <> 0