Oracle SQL查询，根据日期拆分行

您能否帮助我生成所需输出的SQL？

create  table  user_tmp 
(vname varchar(15) , external_pgm varchar(1), eff_date date, exp_date date);

insert 
  into user_tmp 
  values('JOHN','A', '01-JAN-16', '01-APR-16' );

insert 
  into user_tmp 
  values('JOHN','B', '15-FEB-16','01-MAY-16');

COMMIT;

VNAME   A-IND  B-IND  EFF_DATE     EXP_DATE
JOHN      Y     N      01-JAN-16    14-FEB-16
JOHN      Y     Y      15-FEB-16    01-APR-16
JOHN      N     Y      02-APR-16    01-MAY-16

感谢您提供LiveSQL测试。但是我们仍然需要描述生成输出的过程!

我猜在这种情况下，如果程序重叠，你想生成一个新行显示这一点。如果是这样，您可以通过以下方式执行此操作:

-生成第一个eff_date和最后一个exp_date之间的日期
-将您的表连接到这些生成的日期，这些日期介于eff_date和exp_date之间

create  table  user_tmp 
(vname varchar(15) , external_pgm varchar(1), eff_date date, exp_date date);

insert 
  into user_tmp 
  values('JOHN','A', '01-JAN-2016', '01-APR-2016' );

insert 
  into user_tmp 
  values('JOHN','B', '15-FEB-2016','01-MAY-2016');

COMMIT;

with rws as (
  select date'2016-01-01'+level-1 dt from dual 
  connect by level <= (select max(exp_date)-min(eff_date)+1 from user_tmp)
), dates as (
  select dt, vname, external_pgm from rws
  join   user_tmp
  on     dt between eff_date and exp_date
)
  select * from dates;

DT                   VNAME           E
-------------------- --------------- -
01-JAN-2016 00:00:00 JOHN            A
02-JAN-2016 00:00:00 JOHN            A
03-JAN-2016 00:00:00 JOHN            A
...

因此，这一点你有一个日期列表和哪些程序正在运行。您想要的是每个日期的单行，将程序转换为列。即枢轴!

-要显示在列中的值。你只想要一个Y标志，所以用min('Y')
-存储要成为列的值的列，这是您的external_pgm
-要成为列的external_pgm值的列表 ('A'，'B')

这给出了一个pivot子句:

pivot (min('Y') for external_pgm in ('A' a, 'B' b))

将其插入您以前的查询。然后按生成的A和B列分组。(如果需要，此时映射到N)。并返回这些组的最小和最大日期:

with rws as (
  select date'2016-01-01'+level-1 dt from dual 
  connect by level <= (select max(exp_date)-min(eff_date)+1 from user_tmp)
), dates as (
  select dt, vname, external_pgm from rws
  join   user_tmp
  on     dt between eff_date and exp_date
), cols as (
  select * from dates
  pivot (min('Y') for external_pgm in ('A' a, 'B' b))
)
  select vname, nvl(a, 'N'), nvl(b, 'N'), min(dt), max(dt)
  from   cols
  group  by vname,nvl(a, 'N'), nvl(b, 'N')
  order by min(dt);

VNAME           N N MIN(DT)              MAX(DT)            
--------------- - - -------------------- --------------------
JOHN            Y N 01-JAN-2016 00:00:00 14-FEB-2016 00:00:00
JOHN            Y Y 15-FEB-2016 00:00:00 01-APR-2016 00:00:00
JOHN            N Y 02-APR-2016 00:00:00 01-MAY-2016 00:00:00

如果每个程序只发生一次，你就完成了!但是，如果程序A以后可以再次重新启动，则会运行它的问题:

insert  into user_tmp 
  values('JOHN','A', '15-APR-2016', '01-JUN-2016' );
  
with rws as (
  select date'2016-01-01'+level-1 dt from dual 
  connect by level <= (select max(exp_date)-min(eff_date)+1 from user_tmp)
), dates as (
  select dt, vname, external_pgm from rws
  join   user_tmp
  on     dt between eff_date and exp_date
), cols as (
  select * from dates
  pivot (min('Y') for external_pgm in ('A' a, 'B' b))
)
  select vname, nvl(a, 'N'), nvl(b, 'N'), min(dt), max(dt)
  from   cols
  group  by vname,nvl(a, 'N'), nvl(b, 'N')
  order by min(dt);

VNAME           N N MIN(DT)              MAX(DT)            
--------------- - - -------------------- --------------------
JOHN            Y N 01-JAN-2016 00:00:00 01-JUN-2016 00:00:00
JOHN            Y Y 15-FEB-2016 00:00:00 01-MAY-2016 00:00:00
JOHN            N Y 02-APR-2016 00:00:00 14-APR-2016 00:00:00

如果是这种情况，则可以使用Tabibitosan方法将连续的行分组在一起。您可以通过以下方式执行此操作:

-为每个 (a，B) 组分配一个row_number()，按日期排序
-从dt中减去这个数字

这为 (A，B) 对的连续日期提供了相同的值。非连续具有不同的值。所以重复前面的group by，但包括这个表达式。

瞧:

with rws as (
  select date'2016-01-01'+level-1 dt from dual 
  connect by level <= (select max(exp_date)-min(eff_date)+1 from user_tmp)
), dates as (
  select dt, vname, external_pgm from rws
  join   user_tmp
  on     dt between eff_date and exp_date
), cols as (
  select * from dates
  pivot (min('Y') for external_pgm in ('A' a, 'B' b))
), tabibitosan as (
  select vname, nvl(a, 'N') a, nvl(b, 'N') b, dt, 
         dt - row_number() over (
           partition by nvl(a, 'N'), nvl(b, 'N') order by dt
         ) grp
  from   cols
)
  select vname, a, b, min(dt), max(dt) from tabibitosan
  group  by vname, grp, a, b
  order  by min(dt);

VNAME           A B MIN(DT)              MAX(DT)            
--------------- - - -------------------- --------------------
JOHN            Y N 01-JAN-2016 00:00:00 14-FEB-2016 00:00:00
JOHN            Y Y 15-FEB-2016 00:00:00 01-APR-2016 00:00:00
JOHN            N Y 02-APR-2016 00:00:00 14-APR-2016 00:00:00
JOHN            Y Y 15-APR-2016 00:00:00 01-MAY-2016 00:00:00
JOHN            Y N 02-MAY-2016 00:00:00 01-JUN-2016 00:00:00

有关旋转的更多信息，请阅读:https://blogs.oracle.com/sql/entry/how_to_convert_rows_to

和Tabibitosan，请注意:https://www.youtube.com/watch?v=yvimYixXo2Q