1、使用connect by,拆分一条记录
select date '2018-10-26' + (rownum - 1) dt from dual connect by rownum <= (date '2018-11-25' - date '2018-10-26' )
2、编程实现,使用一个存储过程或函数,定义游标,其中tableA是原始表,tableB是新表,v_d表示时间跨度的变量,其他变量
declare
-- Local variables here
v_startTime date;
v_endTime date;
v_mdn int;
v2_startTime date;
v2_endTime date;
v_d int;
cursor cr is
select mdn,startTime,endTime from tableA
where trunc(endTime,'dd') - trunc(startTime,'dd') > 0;
begin
-- Test statements here
open cr;
LOOP
fetch cr into v_mdn,v_startTime,v_endTime;
exit when cr%notfound;
v_d :=trunc(v_endTime,'dd') - trunc(v_startTime,'dd') -1;
dbms_output.put_line(v_d);
insert into tableB values(v_mdn,trunc(v_endTime,'dd'),v_endTime);
loop
exit when v_d=0;
v2_startTime := trunc(v_startTime,'dd') + v_d;
dbms_output.put_line(to_char(v2_startTime,'yyyy-MM-dd'));
v2_endTime := v2_startTime + 1;
insert into tableB values(v_mdn,v2_startTime,v2_endTime);
v_d:=v_d-1;
end loop;
insert into tableB values(v_mdn,v_startTime,trunc(v_startTime,'dd')+1);
end loop;
close cr;
end;
/
3、多条记录拆分,每个id每一天一条数据
---根据一条数据中的开始时间和结束时间将数据拆成多条数据
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
其中:prior是关键,依据它进行分层归类,
如果connect by h后面只有:AND PRIOR TEMPID = TEMPID
则会报错
CONNECT BY loop in user data
原因:
1、使用connect by 层次查询的用法报错:ORA-01436: CONNECT BY loop in user data
产生这个错误的原因,产生了死循环,解决办法有两种如下:
-------------------------------------------------------------------
1、使用 connect by nocycle (不推荐),该方法得不到正确的结果。
2、修改基础数据 。
3、在 connect by的后面加上AND PRIOR DBMS_RANDOM.RANDOM <> 0