对日期拆分
将一个日期段的数据,拆分到具体的每一天
可参考:ORACLE知识点(一):根据一条数据中的开始时间和结束时间将数据拆成多条数据
WITH TEMPTABLE AS(
SELECT
'111' EID,
TO_DATE('2024-10-01','YYYY-MM-DD') STARTDATE,
TO_DATE('2024-10-07','YYYY-MM-DD') ENDDATE
FROM DUAL
)
SELECT
EID,
STARTDATE,
ENDDATE,
TRUNC(STARTDATE) + LEVEL - 1 AS NEWDATE
FROM
TEMPTABLE A
CONNECT BY
LEVEL < TO_NUMBER(TRUNC(ENDDATE)- TRUNC(STARTDATE)) + 2
AND PRIOR EID = EID
AND PRIOR DBMS_RANDOM.RANDOM <> 0
效果:
对时间拆分
将一个时间段的数据,根据时间拆分成多行数据,每一天的9点到18点为一行数据。
WITH TEMPTABLE AS (
SELECT
'1' EID,
TO_DATE('2024-04-25 11:56:28', 'YYYY-MM-DD hh:mi:ss') STARTTIME,
TO_DATE('2024-04-28 11:56:47', 'YYYY-MM-DD hh:mi:ss') ENDTIME
FROM
dual )
SELECT
EID ,
STARTTIME,
ENDTIME ,
CASE
WHEN LEVEL = 1 THEN STARTTIME
ELSE TRUNC(STARTTIME) + INTERVAL '9' HOUR + LEVEL - 1
END AS STARTDATENEW,
CASE
WHEN LEVEL > TO_NUMBER(TRUNC(ENDTIME) - TRUNC(STARTTIME)) THEN ENDTIME
ELSE TRUNC(STARTTIME) + INTERVAL '18' HOUR + LEVEL - 1
END AS ENDDATENEW
FROM
TEMPTABLE A
CONNECT BY
LEVEL < TO_NUMBER(TRUNC(ENDTIME) - TRUNC(STARTTIME)) + 2
AND PRIOR EID = EID
AND PRIOR DBMS_RANDOM.RANDOM <> 0;
效果: