oracle将时间段按半年拆分,求助一个连续时间段拆分SQL

最新推荐文章于 2023-09-18 13:46:20 发布

以网为生

最新推荐文章于 2023-09-18 13:46:20 发布

阅读量345

点赞数

文章标签： oracle将时间段按半年拆分

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

--殊途同归

关注