with t as
(
Select 1 Id,'2013-12-1 1:00' Startdate,'2013-12-2 16:00' Enddate From Dual
UNION ALL
Select 2 Id,'2013-12-3 9:00' Startdate,'2013-12-4 10:00' Enddate From Dual
),
Tmp1 As(
Select Id,To_Date(Startdate,'yyyy-mm-dd hh24:mi') Startdate,
To_Date(ENDDATE,'yyyy-mm-dd hh24:mi') ENDDATE,
To_Char(To_Date(Startdate,'yyyy-mm-dd hh24:mi'),'hh24') Min_S,
To_Char(To_Date(Enddate,'yyyy-mm-dd hh24:mi'),'hh24') Max_E,
Case When To_Number(To_Char(To_Date(Startdate,'yyyy-mm-dd hh24:mi'),'hh24'))<8 Then -1 Else 0 End Left,
Case When To_Number(To_Char(To_Date(Enddate,'yyyy-mm-dd hh24:mi'),'hh24'))>8 Then 1 Else 0 End Right,
trunc(To_Date(enddate,'yyyy-mm-dd hh24:mi')-To_Date(startdate,'yyyy-mm-dd hh24:mi')) inv
From T )
Select Id,To_Char(Startdate+Left+Level-1,'yyyy-mm-dd') 日期,
Decode(Left,-1,Decode(Level,1,To_Char(Startdate,'yyyy-mm-dd hh24:mi'),To_Char(Startdate+Level-2,'yyyy-mm-dd')||' 08:00'),
0,Decode(Level,1,To_Char(Startdate,'yyyy-mm-dd hh24:mi'),To_Char(Startdate+Level-1,'yyyy-mm-dd')||' 08:00')) 开始日期,
Decode(Left,-1,Decode(Level,Abs(Left)+Right+Inv,To_Char(Enddate,'yyyy-mm-dd hh24:mi'),To_Char(Startdate+Level-1,'yyyy-mm-dd')||' 08:00'),
0,Decode(Level,Abs(Left)+Right+Inv,To_Char(Enddate,'yyyy-mm-dd hh24:mi'),To_Char(Startdate+Level,'yyyy-mm-dd')||' 08:00')) 结束日期
From Tmp1 Connect By Level<=Abs(Left)+Right+Inv and prior id=id
And Prior Dbms_Random.Value Is Not Null
--殊途同归