SQL延伸1--计算时间重叠

常常碰到要校验数据范围(时间上)是否存在重叠冲突的情况，典型的场景有检测住院人员出入院时间、酒店房间预订等；
这种问题是在一个表中，针对具体的同一个事物来判断(检测住院人员出入院时间针对同一个住院人员，酒店房间预订则针对同一间房)；


以房间预订为例：
    假如房间888号已经有9月1日-9月10日的预订记录，当其它客人再来预订时，系统必须判断，不能与这个日期范围产生重叠。


有四种情况会产生重叠：
1、全包含：例如用户企图预订8月30日-9月15日；
2、被包含：例如用户企图预订9月5日-9月6日；
3、下限包含：例如用户企图预订8月30日-9月2日；
4、上限包含：例如用户企图预订9月8日-9月15日。


其它三种情况：
1、前期顺预订：例如用户企图预订8月30日-9月1日；
2. 后期顺预订：例如用户企图预订9月10日-9月15日；
3. 日期一致：  例如用户企图预订9月1日-9月10日；


-- 搭建测试表及数据
 create table T_TIME_OVERLAP (
    ID NUMBER(16) not null,
    psn_id  NUMBER(16),
    inbed_dt  DATE,
    outbed_dt DATE,
    bz char(1)
  ) ;   
  alter table T_TIME_OVERLAP add constraint PK_T_TIME_OVERLAP primary key (ID) ;
       
  comment on table T_TIME_OVERLAP is '测试时间重叠表';
  comment on column T_TIME_OVERLAP.ID is '表记录序号';
  comment on column T_TIME_OVERLAP.psn_id  is '个人身份证号';
  comment on column T_TIME_OVERLAP.inbed_dt is  '入日期';
  comment on column T_TIME_OVERLAP.outbed_dt is  '出日期';
  comment on column T_TIME_OVERLAP.bz is  '备注';
  
  INSERT INTO T_TIME_OVERLAP VALUES (1,110123,to_date('2016-09-01','yyyy-mm-dd'),to_date('2016-09-10','yyyy-mm-dd'),'0');   -- 初始数据
  INSERT INTO T_TIME_OVERLAP VALUES (2,110123,to_date('2016-08-30','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'1');   -- 测试全包
  INSERT INTO T_TIME_OVERLAP VALUES (3,110123,to_date('2016-09-05','yyyy-mm-dd'),to_date('2016-09-06','yyyy-mm-dd'),'1');   -- 被包含
  INSERT INTO T_TIME_OVERLAP VALUES (4,110123,to_date('2016-08-30','yyyy-mm-dd'),to_date('2016-09-02','yyyy-mm-dd'),'1'); 
  INSERT INTO T_TIME_OVERLAP VALUES (5,110123,to_date('2016-09-08','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'1'); 
  INSERT INTO T_TIME_OVERLAP VALUES (6,110123,to_date('2016-08-30','yyyy-mm-dd'),to_date('2016-09-01','yyyy-mm-dd'),'2'); 
  INSERT INTO T_TIME_OVERLAP VALUES (7,110123,to_date('2016-09-10','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'2'); 
  INSERT INTO T_TIME_OVERLAP VALUES (8,110123,to_date('2016-09-10','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'3');  
  INSERT INTO T_TIME_OVERLAP VALUES (10,220123,to_date('2016-09-10','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'4');  -- 干扰数据1
  INSERT INTO T_TIME_OVERLAP VALUES (11,220123,to_date('2016-09-01','yyyy-mm-dd'),to_date('2016-09-10','yyyy-mm-dd'),'4');
  INSERT INTO T_TIME_OVERLAP VALUES (13,330123,to_date('2016-09-10','yyyy-mm-dd'),to_date('2016-09-15','yyyy-mm-dd'),'1'); 
  commit ; 
  
 --------- 


正确的写法如下：
  select a.id, b.id
    from T_TIME_OVERLAP a, T_TIME_OVERLAP b
   where a.psn_id = b.psn_id
     and a.id <> b.id
     and (a.inbed_dt between b.inbed_dt and b.outbed_dt or
         a.outbed_dt between b.inbed_dt and b.outbed_dt or
         b.inbed_dt between a.inbed_dt and a.outbed_dt or
         b.outbed_dt between a.inbed_dt and a.outbed_dt)
   order by a.id, b.id; -- 14 行


推倒出一个精简公式：
    a_start <= b_end and a_end >= b_start


-- 查询示例
  select a.id, b.id
  from T_TIME_OVERLAP a, T_TIME_OVERLAP b
  where a.psn_id = b.psn_id 
  and a.id <> b.id
  and (a.inbed_dt between b.inbed_dt and b.outbed_dt
  or a.outbed_dt between b.inbed_dt and b.outbed_dt
  or b.inbed_dt between a.inbed_dt and a.outbed_dt
  or b.outbed_dt  between a.inbed_dt and a.outbed_dt
  )
  order by a.id, b.id ;   -- 14 行
  
  select a.id, b.id
  from T_TIME_OVERLAP a, T_TIME_OVERLAP b
  where a.psn_id = b.psn_id 
  and a.id <> b.id
  and a.bz in ('0','1') 
  and b.bz in ('0','1')
  and a.inbed_dt <= b.outbed_dt 
  and a.outbed_dt >= b.inbed_dt
  order by a.id, b.id ;   --14 行
 -------------------------------------------------- 
  select a.id, b.id
  from T_TIME_OVERLAP a, T_TIME_OVERLAP b
  where a.psn_id = b.psn_id 
  and a.id <> b.id
  and a.bz in ('0','1','2') 
  and b.bz in ('0','1','2')
  and (a.inbed_dt between b.inbed_dt and b.outbed_dt
  or a.outbed_dt between b.inbed_dt and b.outbed_dt
  or b.inbed_dt between a.inbed_dt and a.outbed_dt
  or b.outbed_dt  between a.inbed_dt and a.outbed_dt
  )
  order by a.id, b.id ;  --26行
  
  select a.id, b.id
  from T_TIME_OVERLAP a, T_TIME_OVERLAP b
  where a.psn_id = b.psn_id 
  and a.id <> b.id
  and a.bz in ('0','1','2') 
  and b.bz in ('0','1','2')
  and a.inbed_dt <= b.outbed_dt 
  and a.outbed_dt >= b.inbed_dt
  order by a.id, b.id ;  -- 26行


 -------------------------------------------------- 
  select a.id, b.id
  from T_TIME_OVERLAP a, T_TIME_OVERLAP b
  where a.psn_id = b.psn_id 
  and a.id <> b.id
  and a.bz in ('0','1','2','3') 
  and b.bz in ('0','1','2','3')
  and (a.inbed_dt between b.inbed_dt and b.outbed_dt
  or a.outbed_dt between b.inbed_dt and b.outbed_dt
  or b.inbed_dt between a.inbed_dt and a.outbed_dt
  or b.outbed_dt  between a.inbed_dt and a.outbed_dt
  )
  order by a.id, b.id ;  --34 行
  
  select a.id, b.id
  from T_TIME_OVERLAP a, T_TIME_OVERLAP b
  where a.psn_id = b.psn_id 
  and a.id <> b.id
  and a.bz in ('0','1','2','3') 
  and b.bz in ('0','1','2','3')
  and a.inbed_dt <= b.outbed_dt 
  and a.outbed_dt >= b.inbed_dt
  order by a.id, b.id ;    --34行


 --------------------------------------------------