我希望得到一些帮助来编写一些我自己没有成功的sql.
我有一个数据表:
ID StartDate EndDate 1 01/01/2000 04:30 PM 01/03/2000 06:15 AM 2 01/04/2000 08:10 AM 01/04/2000 07:25 AM 3 01/05/2000 11:00 AM 01/06/2000 03:45 AM
我需要得到以下内容:
ID StartDate EndDate 1 01/01/2000 04:30 PM 01/01/2000 11:59 PM 1 01/02/2000 12:00 AM 01/02/2000 11:59 PM 1 01/03/2000 12:00 AM 01/03/2000 06:15 AM 2 01/04/2000 08:10 AM 01/04/2000 07:25 AM 3 01/05/2000 11:00 AM 01/05/2000 11:59 PM 3 01/06/2000 12:00 AM 01/06/2000 03:45 AM
换句话说,按日分割日期范围.这在sql中甚至可能吗?
可以在sql中执行此操作.有两个技巧.第一个是生成一系列数字,您可以使用连接进行CTE.
第二个是整合正确的逻辑来扩展日期,同时保持开始和结束的正确时间.
以下是一个例子:
with n as ( select level n from dual connect by level <= 20 ),t as ( select 1 as id,to_date('01/01/2000 4','mm/dd/yyyy hh') as StartDate,to_date('01/03/2000 6','mm/dd/yyyy hh') as EndDate from dual union all select 2 as id,to_date('01/04/2000 8',to_date('01/04/2000 12','mm/dd/yyyy hh') as EndDate from dual union all select 3 as id,to_date('01/05/2000','mm/dd/yyyy') as StartDate,to_date('01/06/2000','mm/dd/yyyy') as EndDate from dual ) select t.id,(case when n = 1 then StartDate else trunc(StartDate + n - 1) end) as StartDate,(case when trunc(StartDate + n - 1) = trunc(enddate) then enddate else trunc(StartDate + n) end) from t join n on StartDate + n - 1 <= EndDate order by id,StartDate