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行  --------------------------------------------------