oracle循环找下一个时间

0
同一个ID比如叫做资源有多个时间分配时间（批次），每个分配时间可能有多个联系记录（另一张表），联系记录中没有分配id可以关联，所以要靠时间判断：如果联系时间在本次分配时间和下次分配时间之前，认为是对应在本分配批次中的联系记录。
简言之，新增一个字段，循环查找下一个时间（升序），如果只有一个时间可以留空，多个分配的最后一个分配的对应时间也给空
 麻烦大家给点思路
select 1 rkid,'2016-01-01' distribute_date from dual
union all select 2 rkid,'2016-01-02' distribute_date from dual
union all select 3 rkid,'2016-01-03' distribute_date from dual
union all select 3 rkid,'2016-01-05' distribute_date from dual
union all select 4 rkid,'2016-01-02' distribute_date from dual
union all select 5 rkid,'2016-01-02' distribute_date from dual
union all select 6 rkid,'2016-01-07' distribute_date from dual
union all select 6 rkid,'2016-01-09' distribute_date from dual
union all select 6 rkid,'2016-01-11' distribute_date from dual
union all select 7 rkid,'2016-01-15' distribute_date from dual
QQ图片20170112110239.png
2017-01-12 添加评论
分享

没有找到相关结果

    已邀请:
    1

    老头子 - 专注是唯一的捷径 2017-01-12 回答 

    with ltz as
    
 (select 1 rkid, '2016-01-01' distribute_date
    
    from dual
    
  union all
    
  select 2 rkid, '2016-01-02' distribute_date
    
    from dual
    
  union all
    
  select 3 rkid, '2016-01-03' distribute_date
    
    from dual
    
  union all
    
  select 3 rkid, '2016-01-05' distribute_date
    
    from dual
    
  union all
    
  select 4 rkid, '2016-01-02' distribute_date
    
    from dual
    
  union all
    
  select 5 rkid, '2016-01-02' distribute_date
    
    from dual
    
  union all
    
  select 6 rkid, '2016-01-07' distribute_date
    
    from dual
    
  union all
    
  select 6 rkid, '2016-01-09' distribute_date
    
    from dual
    
  union all
    
  select 6 rkid, '2016-01-11' distribute_date
    
    from dual
    
  union all
    
  select 7 rkid, '2016-01-15' distribute_date from dual)
    
select rkid,
    
       distribute_date,
    
       lead(distribute_date, 1, null) over(partition by rkid order by distribute_date)
    
  from ltz t
    2017-01-12 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册