时间戳

首页 > 解决方案 > SQL 操作

sql - SQL 操作

问题描述

以下是表结构和数据

输入：

 S_ID   PREV_STD    NEXT_STD    CHG_DT
 ------------------------------------------
 1      3           4           23-NOV-16
 1      4           5           20-NOV-17
 1      5           6           17-NOV-18
 2      3           4           20-NOV-17
 3      3           4           23-NOV-16

我想在下面转换上面的内容：09-DEC-18 是系统日期，01-JAN-00 是每个学生第一个标准的开始日期。

期望的输出：

 S_ID   PREV_STD    NEXT_STD    START_DT    END_DT
 --------------------------------------------------
 1      3           4           01-JAN-00   22-NOV-16
 1      4           5           23-NOV-16   19-NOV-17
 1      5           6           20-NOV-17   16-NOV-18
 1      6           6           17-NOV-18   09-DEC-18
 2      3           4           01-JAN-00   19-NOV-17
 2      4           4           20-NOV-17   09-DEC-18
 3      3           4           01-JAN-00   22-NOV-16
 3      4           4           23-NOV-16   09-DEC-18

我的尝试（正在进行中）：

with input as
(
     select 1 s_id, 3 prev_std, 4 next_std, to_date('20161123', 'YYYYMMDD') chg_dt 
     from dual
     union all
     select 1 s_id, 4, 5, to_date('20171120', 'YYYYMMDD') 
     from dual
     union all
     select 1 s_id, 5, 6, to_date('20181117', 'YYYYMMDD') 
     from dual
     union all 
     select 2 s_id, 3, 4, to_date('20171120', 'YYYYMMDD') 
     from dual
     union all
     select 3 s_id, 3, 4, to_date('20161123', 'YYYYMMDD') 
     from dual
)
select 
    S_ID, PREV_STD, NEXT_STD,   
    NVL(lag(CHG_DT) over(partition by s_id order by chg_dt),   
    to_date('20000101','YYYYMMDD')) start_dt,
    CHG_DT - 1 stop_dt,
    CHG_DT,
    NVL(lead(CHG_DT) over(partition by s_id order by chg_dt), sysdate) next_start_dt,
    count(1) over(partition by s_id order by chg_dt) rec_num,
    count(1) over(partition by s_id) rec_to_process
from 
    input;

标签： sqloracle

解决方案

完成了我的解决方案。

with input as(
  select 
    1 s_id, 
    3 prev_std, 
    4 next_std, 
    to_date('20161123', 'YYYYMMDD') chg_dt 
  from 
    dual 
  UNION ALL 
  select 
    1 s_id, 
    4, 
    5, 
    to_date('20171120', 'YYYYMMDD') 
  from 
    dual 
  UNION ALL 
  select 
    1 s_id, 
    5, 
    6, 
    to_date('20181117', 'YYYYMMDD') 
  from 
    dual 
  UNION ALL 
  select 
    2 s_id, 
    3, 
    4, 
    to_date('20171120', 'YYYYMMDD') 
  from 
    dual 
  UNION ALL 
  select 
    3 s_id, 
    3, 
    4, 
    to_date('20161123', 'YYYYMMDD') 
  from 
    dual
) 
select 
  * 
from 
  (
    select 
      S_ID, 
      PREV_STD, 
      NEXT_STD, 
      NVL(
        lag(CHG_DT) over(
          partition by s_id 
          order by 
            chg_dt
        ), 
        to_date('20000101', 'YYYYMMDD')
      ) start_dt, 
      CHG_DT - 1 stop_dt 
    from 
      input 
    UNION ALL 
    select 
      S_ID, 
      NEXT_STD, 
      NEXT_STD, 
      CHG_DT, 
      sysdate 
    from 
      (
        select 
          S_ID, 
          NEXT_STD, 
          CHG_DT, 
          count(1) over(
            partition by s_id 
            order by 
              chg_dt
          ) rec_num, 
          count(1) over(partition by s_id) rec_to_process 
        from 
          input
      ) 
    where 
      rec_num = rec_to_process
  ) 
order by 
  S_ID, 
  start_dt;

推荐阅读